time format in SQL Server

Question

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?

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:

SELECT CONVERT(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

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

Solution 4

Try:

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

Solution 5

If you are using MySql you can use TIME_FORMAT()

Code ↓↓

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

View more solutions

Comments

[Pavan]

why trim when you can just access the time straight away.

[LittleBobbyTables - Au Revoir]

@Pavan - I added the TRIM because there may be a leading space for times other than 10, 11 or 12 AM/PM. I didn't like format 108 because it doesn't include AM/PM like @Jin Yong asked for.

[LittleBobbyTables - Au Revoir]

Welcome to StackOverflow! A few pointers, if I may. (1) You may want to edit your post, highlight your code, and press the '101010' button, it will format the code nicely. (2) I see some C# code in there? It may be better if you provide straight SQL scripts. (3) Your output isn't in AM/PM format.

[deniz]

Explanation of the 108, and alternative styles: docs.microsoft.com/en-us/sql/t-sql/functions/…