Sunday, August 3, 2008

Hacking SQL to Save CFGRID: Date Format

In the HTML format CFGRID, my date "mask" formatting, using the CF documentation, didn't seem to work at all. I don't know why but my impression from cfdocs was that it wasn't going to work in the HTML format grid I was using. I hacked this by changing my SQL to begin with, rather than doing it in CF.

Formerly it was:


SELECT mydatefield as DateEntered

then

<cfgridcolumn
name="mydatefield"
display="yes"
header="Date"
mask="EEE DD-MMM-YY H:NN A">


Which sucked and continued to show me whatever it wanted, rather than the "mask" attributes the cfdocs had. So I changed it to this:


SELECT CONVERT(VARCHAR(11), mydatefield, 106) AS DateEntered

then

<cfgridcolumn
name="mydatefield"
display="yes"
header="Date">



And the resulting output looks like this:

22 Jun 2008

You can google for SQL DATE FORMAT or something like that and see the whole huge list of numbers (mine 106, above) that will format dates differently.

2 comments:

mikeweezer said...

You sir are my hero! If you are ever in Seattle, I'm buying you a beer. Been looking to solve this one for awhile now. Thank you!!!

Chris Ulrich said...

In MS SQL I did this:

CONVERT(VARCHAR(12), DateAdded, 101) AS DateAdded

and got the lovely:

01/27/2009 format

Thanks!