SQL Server Date and Time styles

One of the most frequently question I asked is which date format style number I should to use. Here’s a summary of different date and time style matrix that we can used in T-SQL as part of the CONVERT function.

Matrix of Date and Time style

Style numberInput/Output Format
100 mon dd yyyy hh:miAM (or PM)
1011 = mm/dd/yy
101 = mm/dd/yyyy
1022 = yy.mm.dd
102 = yyyy.mm.dd
1033 = dd/mm/yy
103 = dd/mm/yyyy
1044 = dd.mm.yy
104 = dd.mm.yyyy
1055 = dd-mm-yy
105 = dd-mm-yyyy
1066 = dd mon yy
106 = dd mon yyyy
1077 = Mon dd, yy
107 = Mon dd, yyyy
108hh:mi:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
11010 = mm-dd-yy
110 = mm-dd-yyyy
11111 = yy/mm/dd
111 = yyyy/mm/dd
11212 = yymmdd
112 = yyyymmdd
113 dd mon yyyy hh:mi:ss:mmm (24h)
114hh:mi:ss:mmm (24h)
 120 yyyy-mm-dd hh:mi:ss (24h)
 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
22 mm/dd/yy hh:mi:ss AM (or PM)
23yyyy-mm-dd
126yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
130dd mon yyyy hh:mi:ss:mmmAM
131dd/mm/yyyy hh:mi:ss:mmmAM

Sample convert string to datetime

select convert(datetime, ‘2015/12/31 13:58:03’,111)

select convert(datetime, ‘2012-11-07T18:26:20.096Z’,127)

Leave a Reply