This article provides a reference for the custom date and time format specifiers that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples.

You can combine any of these format specifiers to produce a customized format string. See below for a code example and an explanation on how custom format strings work.

The examples in the table assume a datetimeoffset value of 2080-05-01 23:09:08.1234567 +07:00.

Format String	Description	Example
d	The day of the month, from 1 through 31.	1
dd	The day of the month, from 01 through 31.	01
ddd	The abbreviated name of the day of the week.	Wed
dddd	The full name of the day of the week.	Wednesday
f	The tenths of a second in a date and time value.	1
ff	The hundredths of a second in a date and time value.	12
fff	The milliseconds in a date and time value.	123
ffff	The ten thousandths of a second in a date and time value.	1234
fffff	The hundred thousandths of a second in a date and time value.	12345
ffffff	The millionths of a second in a date and time value.	123456
fffffff	The ten millionths of a second in a date and time value.	1234567
F	If non-zero, the tenths of a second in a date and time value.	1
FF	If non-zero, the hundredths of a second in a date and time value.	12
FFF	If non-zero, the milliseconds in a date and time value.	123
FFFF	If non-zero, the ten thousandths of a second in a date and time value.	1234
FFFFF	If non-zero, the hundred thousandths of a second in a date and time value.	12345
FFFFFF	If non-zero, the millionths of a second in a date and time value.	123456
FFFFFFF	If non-zero, the ten millionths of a second in a date and time value.	1234567
g	The period or era.	A.D.
gg	The period or era.	A.D.
h	The hour, using a 12-hour clock from 1 to 12.	11
hh	The hour, using a 12-hour clock from 01 to 12.	11
H	The hour, using a 24-hour clock from 0 to 23.	23
HH	The hour, using a 24-hour clock from 00 to 23.	23
K	Time zone information.	+07:00
m	The minute, from 0 through 59.	9
mm	The minute, from 00 through 59.	09
M	The month, from 1 through 12.	5
MM	The month, from 01 through 12.	05
MMM	The abbreviated name of the month.	May
MMMM	The full name of the month.	May
s	The second, from 0 through 59.	8
ss	The second, from 00 through 59.	08
t	The first character of the AM/PM designator.	P
tt	The AM/PM designator.	PM
y	The year, from 0 to 99.	80
yy	The year, from 00 to 99.	80
yyy	The year, with a minimum of three digits.	2080
yyyy	The year as a four-digit number.	2080
yyyyy	The year as a five-digit number.	02080
z	Hours offset from UTC, with no leading zeros.	+7
zz	Hours offset from UTC, with a leading zero for a single-digit value.	+07
zzz	Hours and minutes offset from UTC.	+07:00
:	The time separator.	:
/	The date separator.	/
"string"	Literal string delimiter.	string
%	Defines the following character as a custom format specifier.
\	The escape character.

Any other character is copied to the result string unchanged.

Important: When using a single-character custom format string, you should prepend it with the percentage sign (%). Alternatively, you can add a space. If you don’t do this, you’ll either get NULL, or the format specifier could be interpreted as a standard format specifier, and you’ll get unintended results.

What are Custom Format Strings?

A custom format string consists of one or more custom format specifiers. The above table lists the custom format specifiers available for formatting date and time values into a string.

There are also standard date and time format strings. Each of these is an alias for a custom format string. Standard format strings consist of a single format specifier, so they’re quicker to use (but less flexible than custom format strings).

Any format string that is not a standard date and time format string is interpreted as a custom date and time format string.

Example of Usage

The way custom format specifiers work, is that you can combine them together to form a custom format string when using the FORMAT() function. This determines how the result is formatted.

Here are some examples:

DECLARE @date datetimeoffset;
SET @date = '2080-05-01 23:09:08.1234567 +07:00';
SELECT 
  FORMAT(@date, 'd, MMM yy') AS 'd, MMM yy',
  FORMAT(@date, 'dd, MMMM yyyy') AS 'dd, MMMM yyyy',
  FORMAT(@date, 'dddd, MMM dd yyyy') AS 'dddd, MMM dd yyyy',
  FORMAT(@date, 'hh:mm:ss') AS 'hh:mm:ss',
  FORMAT(@date, 'hh:mm tt') AS 'hh:mm tt';

Result:

+-------------+-----------------+------------------------+------------+------------+
| d, MMM yy   | dd, MMMM yyyy   | dddd, MMM dd yyyy      | hh:mm:ss   | hh:mm tt   |
|-------------+-----------------+------------------------+------------+------------|
| 1, May 80   | 01, May 2080    | Wednesday, May 01 2080 | 11:09:08   | 11:09 PM   |
+-------------+-----------------+------------------------+------------+------------+

So it allows for a lot of flexibility in how you present your dates and times.

Here’s an example of using a format string consisting of a single format specifier.

DECLARE @date datetimeoffset;
SET @date = '2080-05-01 23:09:08.1234567 +07:00';
SELECT 
  FORMAT(@date, '%d') AS '%d',
  FORMAT(@date, '%M') AS '%M',
  FORMAT(@date, '%K') AS '%K',
  FORMAT(@date, '%z') AS '%z';

Result:

+------+------+--------+------+
| %d   | %M   | %K     | %z   |
|------+------+--------+------|
| 1    | 5    | +07:00 | +7   |
+------+------+--------+------+

As mentioned, you should prepend these with a percentage sign to avoid getting NULL and so that the format specifier doesn’t inadvertently get interpreted as a standard format string.

Here’s what happens if I remove the percentage sign from the previous example:

DECLARE @date datetimeoffset;
SET @date = '2080-05-01 23:09:08.1234567 +07:00';
SELECT 
  FORMAT(@date, 'd') AS 'd',
  FORMAT(@date, 'M') AS 'M',
  FORMAT(@date, 'K') AS 'K',
  FORMAT(@date, 'z') AS 'z';

Result:

+----------+-------+------+------+
| d        | M     | K    | z    |
|----------+-------+------+------|
| 5/1/2080 | May 1 | NULL | NULL |
+----------+-------+------+------+

We get a completely different result.