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.
Table of Contents
Syntax
We use the format function is as show below
FORMAT( value, format [, culture ] )
Where
value: a valid date expression to formatformat: is the format stringculture: 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' //DD/MM/YYYY select format(@dt,'dd/MM/yyyy') //01/01/2006 //YYYY/MM/DD select format(@dt,'yyyy/MM/dd') //2006/01/01 |
Day, Month & Year Formats
The following tables lists all the format specifiers to format the day, month & year
| Format specifier | Description | |
|---|---|---|
| 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | Declare @dt DateTime set @dt='2020-5-6 12:28 PM' select format(@dt,'d/MM/yyyy') //6/05/2020 select format(@dt,'dd/MM/yyyy') // 06/05/2020 select format(@dt,'ddd/MM/yyyy') // Wed/05/2020 select format(@dt,'dddd/MM/yyyy') // Wednesday/05/2020 select format(@dt,'ddddd/MM/yyyy') // Wednesday/05/2020 |
If the d format used alone, is treated as standard date and time format specifier and returns Short date pattern.
1 2 3 4 | //Single d treated standard date and time format and returns date select format(@dt,'d') //12/16/2020 |
Month Formats
Months uses M. Remember m stands for minutes
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | Declare @dt DateTime set @dt='2020-01-6 12:28 PM' select format(@dt,'dd/M/yyyy') //06/1/2020 select format(@dt,'dd/MM/yyyy') //06/01/2020 select format(@dt,'dd/MMM/yyyy') //06/Jan/2020 select format(@dt,'dd/MMMM/yyyy') 06/January/2020 |
Year formats
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Declare @dt DateTime set @dt='2006-01-01' select format(@dt,'dd/MM/y') //01/01/6 select format(@dt,'dd/MM/yy') //01/01/06 select format(@dt,'dd/MM/yyy') //01/01/2006 select format(@dt,'dd/MM/yyyy') //01/01/2006 select format(@dt,'dd/MM/yyyyy') //01/01/02006 |
Time format strings
| Format Specifier | Description | |
|---|---|---|
| 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
1 2 3 4 5 6 7 8 9 10 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'h:mm:ss') //3:08:07 select format(@dt,'hh:mm:ss') //03:08:07 |
24 Hours Format
1 2 3 4 5 6 7 8 9 10 11 12 | Declare @dt DateTime set @dt='2006-01-01 07:08:07' select format(@dt,'H:mm:ss') //7:08:07 select format(@dt,'HH:mm:ss') //07:08:07 |
Minute Formats
1 2 3 4 5 6 7 8 9 10 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'HH:m:ss') //15:8:07 select format(@dt,'HH:mm:ss') 15:08:07 |
Second Formats
1 2 3 4 5 6 7 8 9 10 11 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'HH:mm:s') //15:08:7 select format(@dt,'HH:mm:ss') //15:08:07 |
Era
1 2 3 4 5 6 7 8 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'HH:mm:ss gg') //15:08:07 A.D. |
Hours from UTC
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'dd-MM-yyyy HH:mm:ss z') //01-01-2006 15:08:07 +5 select format(@dt,'dd-MM-yyyy HH:mm:ss zz') //01-01-2006 15:08:07 +05 select format(@dt,'dd-MM-yyyy HH:mm:ss zzz') //01-01-2006 15:08:07 +05:30 |
AM/PM
1 2 3 4 5 6 7 8 9 10 11 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'dd-MM-yyyy HH:mm:ss t') //01-01-2006 15:08:07 P select format(@dt,'dd-MM-yyyy HH:mm:ss tt') //01-01-2006 15:08:07 PM |
Other format strings
| Format Specifier | description | |
|---|---|---|
| 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 character | The 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
1 2 3 4 5 6 7 8 9 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'d') //1/1/2006 |
There are two ways, in which you can you can change the language
- Use the
culturewhich is the third argument to the format function - 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.
1 2 3 4 5 6 7 8 9 10 | Declare @dt DateTime set @dt='2006-12-01 15:08:07' SET LANGUAGE 'British English' select format(@dt,'d') //01/12/2006 |
To find out the list of supported languages use the following query.
1 2 3 | select * from sys.syslanguages |
The following uses the culture de-de.
1 2 3 4 5 6 7 8 9 | Declare @dt DateTime set @dt='2006-12-01 15:08:07' SET LANGUAGE 'British English' select format(@dt,'d','de-de') 'German' //12.01.2006 MM/dd/yyyy format |
You can refer to list of culture codes.
The following table shows list of all standard format codes.
| Format specifier | Description | Example |
|---|---|---|
| '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 pattern | 2006-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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | Declare @dt DateTime set @dt='2006-01-01 15:08:07' select format(@dt,'d') //1/1/2006 select format(@dt,'D') //Sunday, January 1, 2006 select format(@dt,'f') //Sunday, January 1, 2006 3:08 PM select format(@dt,'F') //Sunday, January 1, 2006 3:08:07 PM select format(@dt,'g') //1/1/2006 3:08 PM select format(@dt,'G') //1/1/2006 3:08:07 PM select format(@dt,'M') //January 1 select format(@dt,'m') //January 1 select format(@dt,'O') //2006-01-01T15:08:07.0000000 select format(@dt,'o') //2006-01-01T15:08:07.0000000 select format(@dt,'R') //Sun, 01 Jan 2006 15:08:07 GMT select format(@dt,'r') //Sun, 01 Jan 2006 15:08:07 GMT select format(@dt,'s') //2006-01-01T15:08:07 select format(@dt,'t') //3:08 PM select format(@dt,'T') //3:08:07 PM select format(@dt,'u') //2006-01-01 15:08:07Z select format(@dt,'Y') //January 2006 select format(@dt,'y') //January 2006 |
