Menu
How to get time part from SQL Server 2005 datetime in HH:mm tt format
E.g.
11:25 AM
14:36 PM
640
We can use DATEPART() function to get the HOUR part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as hour or hh.
By using format code as 111 we can get datetime in “YYYY/MM/DD” format. By using format code as 112 we can get datetime in “YYYYMMDD” format. By using format code as 113 we can get datetime in “DD MMM YYYY HH:mm:ss: fff” format. By using format code as 114 we can get datetime in “HH:mm:ss: fff” format.
One way is:
SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))
If you have a look at Books Online here, format 100 is the one that has the time element in the format you want it in, it's just a case of stripping off the date from the front.
181
You'll need two converts, one to get the HH:mm time, and one to get AM/PM. For example:
declare @date datetime
set @date = '20:01'
SELECT CONVERT(VARCHAR(5), @date, 108) + ' ' +
SUBSTRING(CONVERT(VARCHAR(19), @date, 100),18,2)
This prints:
20:01 PM
In a select query, replace @date with your column's name.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
