AM/PM Date and Time Formats in Microsoft SQL Server
Provided by FMS Development Team
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 and Later
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.
Microsoft SQL Server 2005 and Earlier
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
Microsoft SQL Server 2008 and Later
SQL Server 2008 introduced Date and Time functions to retrieve date portions more easily.
Year, Month and Day Functions
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.
DateName and DatePart Functions
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:
- A string abbreviation for the date type to return
- The DateTime value to evaluate
- Year: DateName("yyyy", GetDate())
- Month: DateName("mm", GetDate())
- Day: DateName("d", GetDate())
- Hour: DateName("hh", GetDate())
- Minute: DateName("n", GetDate())
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):
(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".
See Microsoft's MSDN pages on DateName function and DatePart function for more details.
New Datetime2 Data Type
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.