Format a date

Details about format used for the date functions

The following table defines the token substrings you can use in a date-time format pattern for the functions DATEPARSE(str, format) and DATEFORMAT(date, format).

You can define the format field by combining the tokens shown below.

Usual format token

Examples below given for 2014-08-06T13:07:04.054 considered as a local time in America/New_York

To display	Use this token	Example
year, unpadded	y	2014
two-digit year	yy	14
four- to six- digit year, pads to 4	yyyy	2014
month as an unpadded number	M	8
month as a padded number	MM	08
month as an abbreviated localized string	MMM	Aug
month as an unabbreviated localized string	MMMM	August
month as a single localized letter	MMMMM	A
day of the month, no padding	d	6
day of the month, padded to 2	dd	06
hour in 12-hour time, no padding	h	1
hour in 12-hour time, padded to 2	hh	01
hour in 24-hour time, no padding	H	13
hour in 24-hour time, padded to 2	HH	13
minute, no padding	m	7
minute, padded to 2	mm	07
second, no padding	s	4
second, padded to 2 padding	ss	04
fractional seconds, functionally identical to SSS	u	054
fractional seconds, between 0 and 99, padded to 2	uu	05
fractional seconds, between 0 and 9	uuu	0
millisecond, no padding	S	54
millisecond, padded to 3	SSS	054

Advanced format token

Examples below given for 2014-08-06T13:07:04.054 considered as a local time in America/New_York

To display	Use this token	Example
ordinal (day of year), unpadded	o	218
ordinal (day of year), padded to 3	ooo	218
quarter, no padding	q	3
quarter, padded to 2	qq	03
ISO week year, unpadded	kk	14
ISO week year, padded to 4	kkkk	2014
ISO week number, unpadded	W	32
ISO week number, padded to 2	WW	32
Local week year, unpadded	ii	14
Local week year, padded to 4	iiii	2014
Local week number, unpadded	n	32
Local week number, padded to 2	nn	32
day of the week, as number from 1-7 (Monday is 1, Sunday is 7)	E	3
day of the week, as an abbreviate localized string	EEE	Wed
day of the week, as an unabbreviated localized string	EEEE	Wednesday
day of the week, as a single localized letter	EEEEE	W
localized numeric date	D	9/4/2017
localized date with abbreviated month	DD	Aug 6, 2014
localized date with full month	DDD	August 6, 2014
localized date with full month and weekday	DDDD	Wednesday, August 6, 2014
localized time	t	9:07 AM
localized time with seconds	tt	1:07:04 PM
localized time with seconds and abbreviated offset	ttt	1:07:04 PM EDT
localized time with seconds and full offset	tttt	1:07:04 PM Eastern Daylight Time
localized 24-hour time	T	13:07
localized 24-hour time with seconds	TT	13:07:04
localized 24-hour time with seconds and abbreviated offset	TTT	13:07:04 EDT
localized 24-hour time with seconds and full offset	TTTT	13:07:04 Eastern Daylight Time
short localized date and time	f	8/6/2014, 1:07 PM
less short localized date and time	ff	Aug 6, 2014, 1:07 PM
verbose localized date and time	fff	August 6, 2014, 1:07 PM EDT
extra verbose localized date and time	ffff	Wednesday, August 6, 2014, 1:07 PM Eastern Daylight Time
short localized date and time with seconds	F	8/6/2014, 1:07:04 PM
less short localized date and time with seconds	FF	Aug 6, 2014, 1:07:04 PM
verbose localized date and time with seconds	FFF	August 6, 2014, 1:07:04 PM EDT
extra verbose localized date and time with seconds	FFFF	Wednesday, August 6, 2014, 1:07:04 PM Eastern Daylight Time
narrow offset	Z	+5
short offset	ZZ	+05:00
techie offset	ZZZ	+0500
abbreviated named offset	ZZZZ	EST
unabbreviated named offset	ZZZZZ	Eastern Standard Time
IANA zone	z	America/New_York
meridiem	a	AM
unix timestamp in seconds	X	1407287224
unix timestamp in milliseconds	x	1407287224054