How to get time part from SQL Server 2005 datetime in 'HH:mm tt' 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.
You'll need two converts, one to get the HH:mm time, and one to get AM/PM. For example:
declare @date datetimeset @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.
SQL Server 2008
SELECTCONVERT(TIME,GETDATE()) AS HourMinuteSecond,CONVERT(DATE,GETDATE(),101) AS DateOnlyGO
Result
HourMinuteSecond: 13:06:56.5770000DateOnly: 2012-07-26
SQL Server 2000/2005
SELECTCONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnlyGO