Format Date Field in CFGrid using SQL CONVERT


In ColdFusion AJAX development, have you display a date field in a CFGrid and realise that it always sticks to the same display format? That is, it always displays in this format: mmm, dd yyyy hh:mm:ss, e.g March 27, 2014 22:31:45.

Here’s one way to apply the date format you want for date field columns in a CFGrid by using the SQL CONVERT function in the SQL query. Here’s an example function in a ColdFusion component to illustrate:

<cffunction name=”getCourses” access=”remote” output=”false”>
<cfargument name=”page”>
<cfargument name=”pageSize”>
<cfargument name=”gridsortcolumn”>
<cfargument name=”gridsortdirection”>
<cfscript>var qGet = “”; </cfscript>
<cfquery name=”qGet” datasource=”#request.dsCourse#”>
SELECT
courseID, courseName, CONVERT(varchar(30),courseDate,105) as fmtCourseDate, courseDuration, courseProvider
FROM tblCourses
<cfif arugments.gridsortcolum neq “” and arguments.gridsortdirection neq “”>
ORDER BY #arguments.gridsortcolumn# #arguments.gridsortdirection#
</cfif>
</cfquery>
<cfreturn QueryConvertForGrid(qGet, page, pageSize)>
</cffunction>

Notes:

  • CONVERT(varchar(30),courseDate,105) , in particular the code 105, will format the date field value to show in this format: dd-mm-yyy.
  • For a list of the code for other date formats, visit http://www.w3schools.com/sql/func_convert.asp

Ok, now that we have formatted the date to display in the way we wanted, let’s see how we can use it with the CFGrid. In your front end create a ColdFusion page with the following code fragment (call it whatever you want):

<cfform name=”frmCourses” method=”post”>
<cfgrid format=”html” name=”grdCourses” pageSize=”10″ width=” ‘ auto’ ”
bind=”cfc:#application.componentPath#.courses.getCourses({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})”>
<cfgridcolumn name=”courseID” display=”yes” header=”Course ID” width=”40″ />
<cfgridcolumn name=”courseName” display=”yes” header=”Course Name” width=”50″/>
<cfgridcolumn name=”fmtCourseDate” display=”yes” header=”Course Date” width=”50″ mask=”dd-mm-yyyy”/>
</cfgrid>
</cfform>

Notes:

  • #application.componentPath# points to the location of the component.
  • In this example, I have called the component courses.cfc
  • getCourses is the function (method) found in courses.cfc (content is shown above)
  • mask must match with the format use by the SQL CONVERT function, in this case ‘105’ is the format dd-mm-yyyy

What about editing and saving the date field that we have formatted. How do we ensure what is entered is saved correctly. Let’s create the form and bind the contents from grdCourses CFGrid to pre-populate the form for edit and saving. Here’s a sample code fragment:

<cfform id=”frmUpdate” name=”frmUpdate” method=”post”>
<table>
<tr>
<td><label for=”courseName”>Course Name:</label></td>
<td><cfinput id=”courseName” name=”courseName” type=”text” bind=”{grdCourses.courseName}” size=”40″/></td>
</tr>
<tr>
<td><label for=”courseDate”>Course Date:</label></td>
<td>
<cfinput id=”courseDate” name=”courseDate” type=”datefield” bind=”{grdCourses.fmtCourseDate}” mask=”dd-mm-yyyy” size=”20″/>
</td>
</tr>
<tr>
<td><label for=”courseDuration”>Course Duration:</label></td>
<td><cfinput id=”courseDuration” name=”courseDuration” type=”text” bind=”{grdCourses.courseDuration}” size=”20″/></td>
</tr>
</table>
<cfinput type=”hidden” name=”courseID” bind=”{grdCourses.courseID}”/>
</cfform>

Notes:

  • bind=”{grid-name.tbl-field-name}” specified in the form element is how we bind data from the CFGrid, e.g. grdCourses.courseName will bind pre-populate the input for course name with the value from CFGrid with a name of grdCourses. The value for the courseName is retrieved from the database. See the first function getCourses.
  • ensure cfform and the other coldfusion form equivalent are used, e.g. cfinput.
  • for the datefield, use a cfinput with type = ‘datefield’ and ensure a mask is specify that matches the format used in the SQL CONVERT when retrieving from the database. So if you use dd-mm-yyyy when retrieving, use this same format in the form field mask.

And finally, let’s create a function in the courses component for the form’s action:

<cffunction name=”updateCourses” access=”remote” output=”no” returntype=”void”>
<cfscript>var qUpdate = “”;</cfscript>
<cfquery name=”qUpdate” datasource=”#request.dsCourses#”>
UPDATE tblCourses
SET courseName = <cfqueryparam cfsqltype=”cf_sql_integer” value=”#form.courseName#”>,
courseDate = <cfqueryparam cfsqltype=”cf_sql_date” value=”#DateFormat(form.courseDate,‘dd-mm-yyyy’)#”>,
courseDuration = <cfqueryparam cfsqltype=”cf_sql_varchar” value=”#form.courseDuration#”>
WHERE courseID = <cfqueryparam cfsqltype=”cf_sql_integer” value=”#form.courseID#”>
</cfquery>
</cffunction>

Notes

  • Please note that value submitted from the form must be formatted with the DateFormat function so that it matches the format entered on the form.
  • If the form uses dd-mm-yyyy, then in the SQL update statement, the value for the date field must also use this same format of dd-mm-yyyy.

Final note

There is a drawback  with using the SQL CONVERT function to format the date for use with CFGrid. When clicking on the date column in the CFGrid to sort, it will no longer sort the date properly. This is due to the fact that the SQL CONVERT function has converted the date value to a string.

If the date column sort functionality is important in the CFGrid, then this is not the right solution to use to fix the date format issue with CFGrid.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s