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 number	Input/Output Format
100	mon dd yyyy hh:miAM (or PM)
101	1 = mm/dd/yy 101 = mm/dd/yyyy
102	2 = yy.mm.dd 102 = yyyy.mm.dd
103	3 = dd/mm/yy 103 = dd/mm/yyyy
104	4 = dd.mm.yy 104 = dd.mm.yyyy
105	5 = dd-mm-yy 105 = dd-mm-yyyy
106	6 = dd mon yy 106 = dd mon yyyy
107	7 = Mon dd, yy 107 = Mon dd, yyyy
108	hh:mi:ss
109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	10 = mm-dd-yy 110 = mm-dd-yyyy
111	11 = yy/mm/dd 111 = yyyy/mm/dd
112	12 = yymmdd 112 = yyyymmdd
113	dd mon yyyy hh:mi:ss:mmm (24h)
114	hh: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)
23	yyyy-mm-dd
126	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/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)