Tuesday, February 8, 2011

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)

1 comment:

All suggestions are welcome