SQL Server Date Formats

The format function in SQL Server allows us to display the date in various formats. The SQL Server stores the date & time as integers. But in real worlds the date & time format that people use change from culture to culture. For Example some display the date as DD/MM/YYYY while some as YYYY/MM/DD. Hence it is important to format the date & time as per the users preference before displaying it to the user.

The format function was introduced in SQL 2012. Hence it will not work prior version of SQL like 2008R2, 2008 etc. For these versions use the convert function.

Syntax

We use the format function is as show below

FORMAT( value, format [, culture ] )

Where

value: a valid date expression to format
format: is the format string
culture: culture to use for formatting

Format string

There are two types of format string that is available. One is standard format string & the other one is custom format string.

Custom Format strings

The custom format strings allows us to format the date & time using the format specifiers. We can use them in various combinations to get the correct output.

For Example The following codes displays the given date in DD/MM/YYYY and YYYY/MM/DD formats.

Day, Month & Year Formats

The following tables lists all the format specifiers to format the day, month & year

 Format specifierDescription
Day Formats'd'

The day of the month, from 1 through 31.

If the "d" format specifier is used without other custom format specifiers, it's interpreted as the "d" standard date and time format specified
'dd'The day of the month from 01 through 31.
A single-digit day is formatted with a leading zero.
Day of week'ddd'Abbreviated name of the day of the week
Example Fri
'dddd'full name of the day of the week
Example: Friday
Month'M'month as a number from 1 through 12
'MM'month as a number from 01 through 12
'MMM'abbreviated name of the month
'MMMM'full name of the month
Year'y'year as a one-digit or two-digit number
'yy'year as a two-digit numbe
'yyy'year with a minimum of three digits
'yyyy'year with a minimum of four digits
'yyyyy'The "yyyyy" custom format specifier (plus any number of additional "y" specifiers) represents the year with a minimum of five digits

Examples

Day Formats

If the d format used alone, is treated as standard date and time format specifier and returns Short date pattern.

Month Formats

Months uses M. Remember m stands for minutes

Year formats

Time format strings

 Format SpecifierDescription
The period or era.'g', 'gg'represents the period or era, such as A.D.
12 Hour format'h'hour as a number from 1 through 12
'hh'hour as a number from 01 through 12
24 Hour format'H'hour as a number from 1 through 24
'HH'hour as a number from 01 through 23
Time zone information.'K'Represents the time zone information of a date and time value
minute'm'The minute as a number from 0 through 59
'mm'The minute as a number from 00 through 59
second's'The second as a number from 0 through 59
'ss'The second as a number from 00 through 59
AM/PM't'represents the first character of the AM/PM
'tt'represents the both character of the AM/PM
Hours offset from UTC'z'Represents the signed offset time from the UTC
'zz'Represents the signed offset time from the UTC
'zzz'Represents the signed offset time from the UTC

12 Hour format

24 Hours Format

Minute Formats

Second Formats

Era

Hours from UTC

AM/PM

Other format strings

 Format Specifierdescription
tenths of a second'f' to 'fffffff'Represents seconds fraction.
The number of significant digits displayed is equal to the number of `f`
tenths of a second'F' to 'FFFFFFF'

Represents seconds fraction.
The number of significant digits displayed is equal to the number of `F'

Nothing is displayed if the digit is zero
The time separator.':'represents the time separator,
The Date separator'/'represents the date separator
Literal string delimiter."string"Literal string delimiter.
custom format specifier'%'Defines the following character as a custom format specifier.
The escape character.'\'The escape character.
Any other characterThe character is copied to the result string unchanged.

Standard date & time formats

A standard date and time format string uses a single character as the format specifier.

For Example d format specifier, displays the date in Short date pattern. This will display the date m/d/yyyy format if you are using the language us_english

There are two ways, in which you can you can change the language

  1. Use the culture which is the third argument to the format function
  2. change the language using the SET LANGUAGE TSQL command

The following changes the language to British English. Now the d format string uses the dd/MM/yyyy format.

To find out the list of supported languages use the following query.

The following uses the culture de-de.

You can refer to list of culture codes.

The following table shows list of all standard format codes.

Format specifierDescriptionExample
'd'Short date pattern.1/1/2006
'D'Long date pattern.Sunday, January 1, 2006
'f'Full date/time pattern (short time).Sunday, January 1, 2006 3:08 PM
'F'Full date/time pattern (long time).Sunday, January 1, 2006 3:08:07 PM
'g'General date/time pattern (short time).1/1/2006 3:08 PM
'G'General date/time pattern (long time).1/1/2006 3:08:07 PM
'M', 'm'Month/day pattern.January 1
'O', 'o'round-trip date/time pattern.2006-01-01T15:08:07.0000000
'R', 'r'RFC1123 pattern.Sun, 01 Jan 2006 15:08:07 GMT
's'Sortable date/time pattern.2006-01-01T15:08:07
't'Short time pattern.3:08 PM
'T'Long time pattern.3:08:07 PM
'u'Universal sortable date/time pattern2006-01-01 15:08:07Z
'U'Universal full date/time pattern.Sunday, January 1, 2006 9:38:07 AM
'Y', 'y'Year month pattern.January 2006

Examples

Reference

Leave a Comment

Your email address will not be published.

Scroll to Top