Getting dates to appear correctly in an HTML cfgrid

A little while ago, I posted this to my blog, however I didn't give much context, I just dumped a pair of links to fairly dense articles. What do these links tell you? They tell you how to have SQL Server format things for you (in this case dates), similar to using the dateFormat function in ColdFusion. This can come in very handy when working with cfgrids.

Although the Adobe livedocs say that you can mask / format values that go into a grid, I haven't had much success with getting it to work; especially in the case of HTML grids.

A great work-around is just to use the SQL Server CONVERT function. How? If you plug a date/time/timestamp column name into CONVERT, and tell it what you want to convert it to, it will change the data-type of the date/time column (just in the resulting recordset, not in the actual table) to the data-type of your choice, and format the date for you to one of many predefined formats. Lots of examples of this and other techniques here:

http://www.oreilly.com/news/sqlnut_1200.html

For a list of predefined formats, and some more (not very useful) examples, look here:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

So, if you need to use a date/time column in a cfgrid, just preformat it in the actual query like so:

SELECT CONVERT(VARCHAR(11), date_column, 102) AS formatted_date
FROM table
...etc.

I hope that makes more sense.