There are times when you need to display time in AM/PM format (non-Military or 24 hour) such as 2:07 PM.
This was never difficult in Microsoft Access/VBA using it's Format command:
Format(Now(), "hh:mm AM/PM")
However, it was very complicated in earlier versions of SQL Server. Fortunately, it has gotten progressively easier. We provide a summary of the different options. You may need to still use the older options if you need to support databases hosted on legacy versions of SQL Server.
For the examples below, we use this time:
Microsoft SQL Server 2012 introduced the Format function. You can now get the time very easily:
SELECT Format(GetDate(), 'hh:mm tt') returns 02:07 PM
If you use the abbreviated form (h:m rather than hh:mm), leading zeros are omitted:
SELECT Format(GetDate(), 'h:m tt') returns 2:7 PM
See Microsoft's TechNet page on the Format function for more details.
The standard Convert formats did not have an option to return the time in this format.
The SQL below returns only the time portion of the current date/time in the AM/PM format using the built-in GetDate() function. Replace that with a Field name if you want to convert a value in a field:
SELECT GetDate() AS Example, substring(convert(varchar(20), GetDate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)
This works based on the convert(varchar(30), GetDate(), 9) function.
For example, converts our sample time to:
Sep 6 2016 2:07:08.510PM
This then allows the SELECT statement above to return:
The first expression grabbed the time (5 characters) from the DateTime's 13th character. The second grabs the 2 characters at the end for the AM/PM portion. The nice thing about this approach is that it will work on all versions of SQL Server.
SQL Server 2008 introduced Date and Time functions to retrieve date portions more easily.
For instance, you can easily create columns that are for Year, Month and Day:
SELECT Year(GetDate()), Month(GetDate()), Day(GetDate())
See Microsoft's MSDN page on Date and Time Data Types and Functions for more details.
Alternatively, you can use the DateName and DatePart functions to return a string or integer respectively for the specified value.
These functions take two parameters:
Here's a list of the Date Part abbreviations and example results for '2016-09-06 14:07:08.510 +05:00' (September 6, 2016):
|Date Part||Abbreviation||DateName Return Value Examples
|DatePart Return Value Examples
(always 4 digit year)
(1 is Sunday)
|Time Zone Offset
Note that "y" is not year and "m" is month, not minute which is "n".
Additionally SQL Server 2008 introduced the Datetime2 format to improve upon the Datetime format. For Access databases, the Datetime2 format should be used because is supports the time precision that Access Jet databases have always supported. For more information, visit the Microsoft MSDN page Datetime2 Data Type for more details.
Of course, you can't use these SQL Server 2008 functions if you need to provide backward compatibility to SQL Server 2005 or earlier. But if that's not a concern, these functions significantly simplify date/time manipulation.