QueryConvertForGrid date format woes

Posted At : July 3, 2007 11:59 PM | Posted By : Gary Gilbert
Related Categories: Ajax,Coldfusion,Adobe

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...

Comments (2) |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 3690 Views

Related Blog Entries

Coldfusion 8 CFGRID Column Renderer Update (June 27, 2007)

2 Comments to "QueryConvertForGrid date format woes"- Add Yours

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

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

[Add Comment]

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

QueryConvertForGrid date format woes

Subscribe

Tags

Recent Entries

Blogroll

Wish List