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

Solution 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.

Solution 2:

You can use the CONVERT function like this:

SELECTCONVERT(varchar, your_datetime, 108)

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

Solution 3:

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

SELECTRIGHT('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

Solution 4:

Try:

selectconvert(varchar, getdate(), 108)
+' '+RIGHT(convert(varchar, getdate(), 100), 2) asTime

Solution 5:

If you are using MySql you can use TIME_FORMAT()

Code ↓↓

SELECT name, time_format(datatime,'%H:%i') as tine from cuatomer