time format in SQL Server

问题:

Does anyone know how can I format a select statement datetime value to only display time in SQL Server?

example:

Table cuatomer
id   name   datetime
1    Alvin  2010-10-15 15:12:54:00
2    Ken    2010-10-08 09:23:56:00

When I select the table I like the result will display as below

id   name    time
1    Alvin   3:12PM
2    Ken     9:23AM

Any way that I can do it in mssql?

回答1:

You can use a combination of CONVERT, RIGHT and TRIM to get the desired result:

SELECT ltrim(right(convert(varchar(25), getdate(), 100), 7))

The 100 you see in the function specifies the date format mon dd yyyy hh:miAM (or PM), and from there we just grab the right characters.

You can see more about converting datetimes here.

回答2:

You can use the CONVERT function like this:

SELECT CONVERT(varchar, your_datetime, 108)

However, this is 24-hour clock, no AM/PM.

回答3:

This will get the time from a datetime value and also give the am or pm add on

SELECT RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),8)),7) 

will always return the date in HH:mmAM format.

Note the lack of space

Or

SELECT REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),7)),7),'AM',' AM'),'PM',' PM')

will always return the date in HH:mm AM format.

Hope that helps.

PK

回答4:

Try:

select convert(varchar, getdate(), 108)
+ ' ' + RIGHT(convert(varchar, getdate(), 100), 2) as Time

回答5:

You might be able to use:

select

convert(varchar,getdate(),114)

You might be able to manually construct the query like:

string query = string.Format("INSERT INTO test (DateOnlyField, TimeOnlyField) VALUES ('{0}', '1899-12-30 {1}')", DateTime.Today.ToShortDateString(), TimeString)

I dunno if this might work to:

Create Table Schedule( ScheduleID Integer Identity, ScheduledTime DateTime )

Go

Insert Into Schedule( ScheduledTime ) Values( '10:15:00 AM' )

Go

Select ScheduledTime As DBScheduledTime, Convert( VarChar( 10 ), ScheduledTime, 114 ) As ScheduledTime

From Schedule

Go

Drop Table Schedule

Go

回答6:

If You are using SQL Server 2008 or Higher You can use the following statement:

SELECT Convert( VarChar( 10 ), CAST([columnName] AS TIME(0)), 100 )