This article provides a list of the standard date and time format strings that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples using different cultures and data types.
These are the standard date and time format specifiers that are supported by the .NET Framework, and therefore, SQL Server. Each standard date and time format string is an alias for a custom date and time format string.
US English Examples with ‘datetime2’
The following table contains all of the standard date and time format specifiers.
The examples assume a datetime2(7) value of 2030-05-25 23:59:30.1234567 and use en-us as the value of the culture argument (for US English).
| Format String | Example |
|---|---|
d |
5/25/2030 |
D |
Saturday, May 25, 2030 |
f |
Saturday, May 25, 2030 11:59 PM |
F |
Saturday, May 25, 2030 11:59:30 PM |
g |
5/25/2030 11:59 PM |
G |
5/25/2030 11:59:30 PM |
M or m |
May 25 |
O or o |
2030-05-25T23:59:30.1234567 |
R or r |
Sat, 25 May 2030 23:59:30 GMT |
s |
2030-05-25T23:59:30 |
t |
11:59 PM |
T |
11:59:30 PM |
u |
2030-05-25 23:59:30Z |
U |
Saturday, May 25, 2030 11:59:30 PM |
Y or y |
May 2030 |
US English with ‘datetimeoffset’
Most results are the same when using a datetimeoffset value. However, the O/o format strings have a different result (the result preserves time zone information), and the U format string results in NULL (this is because the datetimeoffset data type doesn’t support the U format specifier).
The following examples assume a datetimeoffset value of 2030-05-25 23:59:30.1234567 +07:00 and use a culture argument of en-us.
| Format String | Example |
|---|---|
d |
5/25/2030 |
D |
Saturday, May 25, 2030 |
f |
Saturday, May 25, 2030 11:59 PM |
F |
Saturday, May 25, 2030 11:59:30 PM |
g |
5/25/2030 11:59 PM |
G |
5/25/2030 11:59:30 PM |
M or m |
May 25 |
O or o |
2030-05-25T23:59:30.1234567+07:00 |
R or r |
Sat, 25 May 2030 16:59:30 GMT |
s |
2030-05-25T23:59:30 |
t |
11:59 PM |
T |
11:59:30 PM |
u |
2030-05-25 16:59:30Z |
U |
NULL* |
Y or y |
May 2030 |
* The DateTimeOffset data type doesn’t support the “U” format specifier, so the result is NULL. However, if we used a datetime2 value and dropped the offset, we would get Saturday, May 25, 2030 11:59:30 PM.
Invariant Culture Examples with ‘datetime2’
The following examples use the invariant culture. The invariant culture is culture-insensitive; it is associated with the English language but not with any country/region. When formatting a date/time value with FORMAT(), you can specify the invariant culture by passing 'iv' as the third argument.
These examples assume a datetime2(7) value of 2030-05-25 23:59:30.1234567 and use iv for the culture argument (which is used to specify the invariant culture).
| Format String | Example |
|---|---|
d |
05/25/2030 |
D |
Saturday, 25 May 2030 |
f |
Saturday, 25 May 2030 23:59 |
F |
Saturday, 25 May 2030 23:59:30 |
g |
05/25/2030 23:59 |
G |
05/25/2030 23:59:30 |
M or m |
May 25 |
O or o |
2030-05-25T23:59:30.1234567 |
R or r |
Sat, 25 May 2030 23:59:30 GMT |
s |
2030-05-25T23:59:30 |
t |
23:59 |
T |
23:59:30 |
u |
2030-05-25 23:59:30Z |
U |
Saturday, 25 May 2030 23:59:30 |
Y or y |
2030 May |
Invariant Culture with ‘datetimeoffset’
These examples assume a datetimeoffset value of 2030-05-25 23:59:30.1234567 +07:00 and use the iv culture.
| Format String | Example |
|---|---|
d |
05/25/2030 |
D |
Saturday, 25 May 2030 |
f |
Saturday, 25 May 2030 23:59 |
F |
Saturday, 25 May 2030 23:59:30 |
g |
05/25/2030 23:59 |
G |
05/25/2030 23:59:30 |
M or m |
May 25 |
O or o |
2030-05-25T23:59:30.1234567+07:00 |
R or r |
Sat, 25 May 2030 16:59:30 GMT |
s |
2030-05-25T23:59:30 |
t |
23:59 |
T |
23:59:30 |
u |
2030-05-25 16:59:30Z |
U |
NULL* |
Y or y |
2030 May |
* The DateTimeOffset data type doesn’t support the “U” format specifier, so the result is NULL. However, if we used a datetime2 value and dropped the offset, we would get Saturday, 25 May 2030 23:59:30.