Links

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