Microsoft SQL Server ResourcesAM/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:

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

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:

  1. A string abbreviation for the date type to return
  2. 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.

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.

Free Product Catalog from FMS