CFGRID DateFormat

For some reason when you have a datetime field in a cfgrid column it displays in a long format like this: Nov 17 2006 01:00:00

Fig 1 (Below) A date displayed in cfgrid

If we work a little SQL magic (using “CONVERT”) in the cfquery though we can do the formatting we want, which is: 11/17/2006. Refer to the “styles” listed in Figure 3 to see what formatting is available.

Coldfusion Query:

SELECT note_id,note_text,
CONVERT(varchar,note_date,101) as note_date
FROM tbl_notes
ORDER BY note_date


Fig 2 (Below) A date displayed in cfgrid after using CONVERT in cfquery

Fig 3 (Below) A helpful list of “styles” to use with the SQL CONVERT function

Style ID	Style Type
0 or 100	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
13 or 113	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
20 or 120	yyyy-mm-dd hh:mi:ss(24h)
21 or 121	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM