Friday, June 19, 2009

Formatting Dates with T-SQL

I've seen a lot of weird and extravagant ways to format dates, and wanted to share a simple method for displaying dates with as little code as possible.

CONVERT (VARCHAR, column_name, StyleID)

Example StyleIDs are listed below:


0 or 100
101
102
103
104
105
106
107
108
9 or 109
110
111
112
13 or 113
114
20 or 120
21 or 121
126
130
131

mon dd yyyy hh:miAM (or PM)
mm/dd/yy
yy.mm.dd
dd/mm/yy
dd.mm.yy
dd-mm-yy
dd mon yy
Mon dd, yy
hh:mm:ss
mon dd yyyy hh:mi:ss:mmmAM (or PM)
mm-dd-yy
yy/mm/dd
yymmdd
dd mon yyyy hh:mm:ss:mmm(24h)
hh:mi:ss:mmm(24h)
yyyy-mm-dd hh:mi:ss(24h)
yyyy-mm-dd hh:mi:ss.mmm(24h)
yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
dd mon yyyy hh:mi:ss:mmmAM
dd/mm/yy hh:mi:ss:mmmAM

Swidget

You May Like This