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:
… and there you have it! I hope you found this tutorial helpful! Good luck and happy querying!