AM/PM Date and Time Formats in Microsoft SQL Server

Provided by the 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:

2016-09-06 14:07:08.510

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 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

For example:

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):

Date Part	Abbreviation	DateName Return Value Examples (as strings)	DatePart Return Value Examples (as integers)
year (always 4 digit year)	yy, yyyy	2016	2016
quarter	qq, q	3	3
month	mm, m	September	9
dayofyear	dy, y	250	250
day	dd, d	6	6
week	wk, ww	37	37
weekday	dw	Tuesday	3 (1 is Sunday)
hour	hh	14	14
minute	mi, n	7	7
second	ss, s	8	8
millisecond	ms	510	510
microsecond	mcs	510000	510000
nanosecond	ns	510000000	510000000
Time Zone Offset (in minutes)	tz	+05:00	300
ISO_WEEK	isowk,isoww	36	36

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.

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:

2:07 PM

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.

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.