Back to cookbooks list Articles Cookbook

How to Change Date and Time Formats in T-SQL

  • FORMAT()

Problem:

You’d like to change the format of a date field or value in a SQL Server database.

Example:

Our database has a table named Patient with data in the columns Id, FirstName, LastName, and RegistrationDate.

IdFirstNameLastNameRegistrationDate
1JaneWilliams2019-06-20
2GabrielBrown2019-02-02
3LoraFolk2016-11-05

Let’s change the format of each patient’s registration date. We’ll put the name of the weekday first, followed by the month day and name and a 4-digit year (e.g. “Friday, 27 December 2019”).

Download SQL Server Cheat Sheet

Solution:

We’ll use the FORMAT() function to change the format of the date in the RegistrationDate column.

SELECT  FirstName,
        LastName,
        FORMAT(RegistrationDate ,'dddd, d MMMM, yyyy')
          AS FormattedRegistrationDate
FROM Patient;

Here’s the result of the query:

FirstNameLastNameFormattedRegistrationDate
JaneWilliamsThursday, 20 June, 2019
GabrielBrownSaturday, 2 February, 2019
LoraFolkSaturday, 5 November, 2016

Discussion:

We can change how a given date is displayed with the FORMAT() function. This function takes two mandatory arguments and one optional argument. In our example, we used only the two mandatory arguments. The first is a date, which can be from a date/time/datetime column or any expression that returns a date or time. (In our example, we use the column RegistrationDate.) The second argument is a string containing the new date format. In our example, we used 'dddd, dd MMMM, yyyy':

  • dddd – The name of the day of the week.
  • d – The day of the month, from 1 to 31.
  • MMMM – The full name of the month.
  • yyyy – The four-digit year.

The table below presents more date/time format specifiers:

specifierdescription
dDay in the range 1-31
ddDay in the range 01-31
dddThe abbreviated name of the day of the week
ddddThe full name of the day of the week
MMonth from 1 to 12
MMMonth from 01 to 12
MMMThe abbreviated name of the month
MMMMThe full name of the month
y2-digit year, from 0 to 99
yy2-digit year from 00 to 99
yyyy4-digit year
gEra (e.g. A.D.)
hHour from 1 to 12 (12-hour clock)
hhHour from 01 to 12 (12-hour clock)
HHour from 0 to 23 (24-hour clock)
HHHour from 00 to 23 (24-hour clock)
mMinute from 0 to 59
mmMinute from 00 to 59
sSecond from 0 to 59
ssSecond from 00 to 59
tFirst character of AM or PM (e.g. 9A, 5P)
ttAM or PM
zHours offset, without leading zero (e.g. +3)
zzHours offset, with leading zero (e.g. +03)

You can learn more in the Microsoft documentation for standard date and time formats and custom formats.

If you would like to format this date for a specific language and/or country, use the third optional argument: culture. This argument is a string containing the culture code for a particular region or country. (Culture codes are made up of a language code, a dash, and a country code.) In the example below, we use the culture code for Germany, which combines the German language code (‘de’) with the country code for Germany (‘DE’), i.e. ‘de-DE’. The resulting date is displayed in German and formatted as a German audience would expect to see it.

SELECT  FirstName,
        LastName,
        FORMAT(RegistrationDate ,'dddd, d MMMM, yyyy', 'de-DE')
          AS FormattedRegistrationDate
FROM Patient;

Here’s the result of this query:

FirstNameLastNameRegistrationDate
JaneWilliamsDonnerstag, 20 Juni, 2019
GabrielBrownSamstag, 2 Februar, 2019
LoraFolkSamstag, 5 November, 2016

Recommended courses:

Recommended articles:

See also: