Get 12-Hour Time from Datetime Datatype in SQL Server 2005 by Ken Baker

It's not as simple as you may think to extract the time in a 12-hour format (as opposed to 24-hour, military format) from a datetime field in SQL Server 2005.   Let me walk you through the solution.

First, let's select the datetime field.

SELECT getdate() AS MyTime

If you didn't want to use the current time, then you can of course substitute the appropriate field in like so:

SELECT mydatetimefield AS MyTime FROM mytable

For simplistic sake, I'll just use getdate() going forward in this tutorial as my source datetime field.  The result should result in the current date and time: 2010-11-07 12:04:17.903

Next, we're going to convert the current datetime field to a varchar format using style 100 which will include AM/PM.

SELECT CONVERT(VARCHAR, getdate(), 100) AS MyTime

The above query returns the result Nov  7 2010 12:04PM

Now we're simply going to use the substring function to extract the appropriate parts of the varchar (hour, minute, AM/PM).

SELECT SUBSTRING(CONVERT(VARCHAR, getdate(), 100), 13, 2) AS MyHour
SELECT SUBSTRING(CONVERT(VARCHAR, getdate(), 100), 16, 2) AS MyMin
SELECT SUBSTRING(CONVERT(VARCHAR, getdate(), 100), 18, 2) AS MyAMPM

The first argument of substring represents the string that we're using, the second argument is the starting position of the substring we're extracting, and the third argument is the length of the string.

Note that the starting position of the substring for the hour is 13.  Even if the day of the month was 2 characters in length, rather than one, it would still have a starting position of 13 because the previous convert function resulted in two spaces between the month and day for days that are single digits.

Now all we need to do is concatenate the three strings as we'd like it displayed.

SELECT SUBSTRING(CONVERT(varchar, getdate(), 100), 13, 2) + ':'
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 16, 2) + ' '
+ SUBSTRING(CONVERT(varchar, getdate(), 100), 18, 2) AS MyTime

This would result in the string we desire:

12:04 PM

... and there you have it!  I hope you found this tutorial helpful!  Good luck and happy querying!


Tweet this article!