QueryConvertForGrid date format woes

I have come to realize that the implementation of the Ajax functionality has some limitations in flexibility. Something as simple as specifying the output format of a date in the grid for example is missing.

Let me start from the beginning.

I have a function that does a directory listing lets say for example sake the cfdocs directory under the webroot and then returns the files in JSON format.


{"TOTALROWCOUNT":6,"QUERY":{"COLUMNS":["NAME","SIZE","DIRECTORY","DATELASTMODIFIED"],"DATA":[["copyright.htm",3026,"\\\\cfdocs",
"May, 31 2007 10:23:17"],["dochome.htm",3257,"\\\\cfdocs","May, 31 2007 10:23:17"],["newton.js",2028,"\\\\cfdocs","May, 31 2007 10:23:24"],["newton_ie.css",3360,"\\\\cfdocs","May, 31 2007 10:23:24"],["newton_ns.css",4281,"\\\\cfdocs","May, 31 2007 10:23:24"],["toc.css",244,"\\\\cfdocs","May, 31 2007 10:23:24"],[null,null,null,null],[null,null,null,null],[null,null,null,null],
[null,null,null,null]]}}

Notice the date time format. It's correct, it is returning the correct date and time but I haven't specified anywhere that specific return format, perhaps I only want to return the date in "mm/dd/yyyy" format.

In the flash version of the cfgrid I simply add a mask attribute and specify how I would like it to be displayed. But with the HMTL version I am out of luck, unless I do a work around, but in my opinion I shouldn't have to!

But I did do a work around so that I could have the output format I want. It meant formatting the date in my query of queries like so:


        <cfdirectory action="list" directory="#expandpath('#path#')#" name="files"/>
    <cfquery name="getFiles" dbtype="query">
        select name,size,'#webpath#' as directory ,datelastmodified
        from files
        where type='File'
        <cfif gridsortcolumn neq ''>
        order by #gridsortcolumn# #gridsortdirection#
        </cfif>
    </cfquery>

        <cfset mq = queryNew("name,size,directory,datelastmodified","varchar,integer,varchar,varchar")>
        <cfset i = 0>
        <cfoutput query="getfiles">
                <cfset i = i + 1/>
                <cfset temp = queryAddRow(mq,1)/>
                <cfset temp= querySetCell(mq,"name","#name#",i)/>
                <cfset temp= querySetCell(mq,"size","#size#",i)/>
                <cfset temp= querySetCell(mq,"directory","#directory#",i)/>
                <cfset temp= querySetCell(mq,"datelastmodified","#dateformat(dateLastModified,'short')#",i)/>
        </cfoutput>
<cfreturn queryConvertForGrid(mq,arguments.page,arguments.pageSize)/>

which resulted in the following JSON output from the queryConvertForGrid:


{"TOTALROWCOUNT":6,"QUERY":{"COLUMNS":["NAME","SIZE","DIRECTORY","DATELASTMODIFIED"],"DATA":[["copyright.htm",3026,"\\\\cfdocs","05\/31\/2007"],
["dochome.htm",3257,"\\\\cfdocs","05\/31\/2007"],["newton.js",2028,"\\\\cfdocs","05\/31\/2007"],["newton_ie.css",3360,"\\\\cfdocs","05\/31\/2007"],
["newton_ns.css",4281,"\\\\cfdocs","05\/31\/2007"],["toc.css",244,"\\\\cfdocs","05\/31\/2007"],[null,null,null,null],[null,null,null,null],
[null,null,null,null],[null,null,null,null]]}}

And thats what I wanted to begin with. Is there a way to specify the output format and I just haven't found it yet? Is there a way to manipulate the Grid object with javaScript prior to it being displayed in the browser?

I have already received bad news about having a custom column renderer I wonder what the response will be from adobe this time? Hopefully some good news.

Until then, Happy Coding...

Related Blog Entries

2 Comments to "QueryConvertForGrid date format woes"- Add Yours
Henry Ho's Gravatar Good news!

I found a way that can bypass the date formatting by queryConvertForGrid(), and also can use Ext.util.Format.dateRenderer()


in MS-SQL:

SELECT convert(varchar,NM.createDate, 1) + ' ' + convert(varchar,NM.createDate, 108) AS Date
FROM XXX

Since this is the format that Ext1.0 date object takes, (see: http://extjs.com/deploy/ext-1.0.1/docs/output/Date...... ), we can use Ext.util.Format.dateRenderer() without any additional JS!!!


Below is a more complete example:

<cfsavecontent variable="head">
<script src="/CFIDE/scripts/ajax/ext/package/date.js" type="text/javascript"></script>
<script type="text/javascript">
applyCustomRenderer = function() {
grid = ColdFusion.Grid.getGridObject('newMeterGrid');
cm = grid.getColumnModel();

cm.setRenderer(0, Ext.util.Format.dateRenderer('Y-m-d'));
// 0 is the first cfgridcolumn

grid.reconfigure(grid.getDataSource(), cm);
};
</script>
</cfsavecontent>

<cfhtmlhead text="#head#">
<cfset ajaxOnLoad("applyCustomRenderer")>
# Posted By Henry Ho | 6/1/08 6:36 PM
Paul Roe's Gravatar Hey Henry your solution put me on the right track but i made one change.

Instead of:

SELECT convert(varchar,NM.createDate, 1) + ' ' + convert(varchar,NM.createDate, 108) AS Date
FROM XXX

Do this instead:
SELECT convert(varchar,NM.createDate, 101) + ' ' + convert(varchar,NM.createDate, 108) AS Date
FROM XXX

Using just a 1 for the type of the first date will not include the century and so you get results like 1905 instead of 2005 when you display the whole year. 101 give you the century and extjs handels it fine:
# Posted By Paul Roe | 11/17/09 7:34 AM

Powered By Railo

Subscribe

Subscribe via RSS
Follow garyrgilbert on Twitter Follow me on Twitter
Or, Receive daily updates via email.

Tags

adobe air ajax apple cf community cfml coldfusion examples ext flash flex google javascript max2007 max2008 misc open source programming railo software technology ui

Recent Entries

No recent entries.

Blogroll

An Architect's View
CFSilence
Rey Bango
TalkingTree

Wish List

My Amazon.com Wish List