20 Most Commonly Used DateTime Formats

SQL Server: 20 Most Commonly Used DateTime Formats

Dealing with datetime data type and conversion of datetime data into many formats is everyday requirement. Here is a table of commonly used formats, to deal with datetime data.

Note: Use Convert function as follow

SELECT CONVERT(VARCHAR(20), GETDATE(), 108)

Format No	Output
101	U.S. mm/dd/yyyy
102	ANSI yy.mm.dd
103	British/French dd/mm/yyyy
104	German dd.mm.yy
105	Italian dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mi:ss
109	mon dd yyyy hh:mi:ss:mmmAM
110	USA mm-dd-yy
111	JAPAN yy/mm/dd
112	Yymmdd
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)
126	yyyy-mm-ddThh:mi:ss.mmm
127	yyyy-mm-ddThh:mi:ss.mmmZ (With Time Zone)
130	dd mon yyyy hh:mi:ss:mmmAM (Hijri)
131	dd/mm/yy hh:mi:ss:mmmAM (Hijri)