CODEWITHSUNDEEP
sqlsql-server-2012
anatp_123
anatp_123

Reputation: 1205

How to show the time in AM/PM format?

How can I get the timestamphour column to show only the time like 5 AM, 6 AM etc?

SELECT dateadd(hour, datediff(hour , 0, callstartdt), 0) as TimeStampHour, Count(*) as count
FROM table_one
where month(CallStartDt)=5 and day(callstartdt) = 16
GROUP BY dateadd(hour, datediff(hour, 0, callstartdt), 0)
ORDER BY dateadd(hour, datediff(hour, 0, callstartdt), 0);

TimeStampHour               count
2016-05-16 05:00:00.000     1
2016-05-16 06:00:00.000     4
2016-05-16 07:00:00.000     4
2016-05-16 08:00:00.000     113
2016-05-16 09:00:00.000     110
2016-05-16 10:00:00.000     131

I'm using sql-2012.

Upvotes: 2

Views: 2821

Answers (1)

EoinS
EoinS

Reputation: 5482

If you only want to show the hour with AM, PM try this:

SELECT FORMAT(dateadd(hour, datediff(hour , 0, callstartdt), 0),'hh tt') AS TimeStampHour ....

This takes the rounded hour that you had and uses Format() to show only hour : 'hh' and AM/PM : 'tt'. You can also show the full time with AM/PM using Format(GetDate(),100).

The DATEPART solution in the comments should also work.

http://sqlfiddle.com/#!6/9eecb7/7727

Upvotes: 2

Related Questions