DESCRIPTION

So, you're having Problems with Dates?

Struggling with dates in ColdFusion can be sometimes quite an hazardeous adventure. The problem, or should we say the many problems, have several origins:

1. There are several formats used in everyday's life to represent a date or an abreviation of a date. The two most widely used are the American format (mm/dd/yyyy) and the European format (dd/mm/yyyy)
2. In both formats, not all combinations of characters represent a valid date, so a verification must be done somewhere between the entry in a form and storing the date in a database, otherwise the user will receive some kind of unfriendly protest from the indignant database;
3. There is no standard in SQL to represent a date; SQL provides formats for text and numeric fields, but not for dates. Of course, no decent database system can be imagined without some facility to manage dates, so every one supports this kind of data, but with its own particularities;
4. And last but not least, ColdFusion has a deadly tentency to guess in what format a date is, when time comes to convert it into a suitable form for the database. If you are not working with American dates, errors are almost as automatic as date conversion! The problem is when a date is represented as a string and a formal date type is needed. The "rule", if one can qualify this a rule, is that when the date in the string can be interpreted as an american date, CF will assume it is an American date (by example, 12/07/2003 becomes December 7^th 2003); but if the date cannot be translated acording to the American format, then the European format is used (by example, 13/07/2003 becomes July 13^th 2003);

The Solution: alway use dates in ODBC format in queries

Fortunately, all data going to ODBC or JDBC datasources transit via the ODBC/JDBC layer, and ODBC does have a standard for dates: the ODBC date format. If one presents a date in a query in ODBC format, he will be guaranteed a safe transfered of the date with no further conversion and that it will be stored correctly in the database. ColdFusion has a tool to create an ODBC date: the ubiquituous createODBCdate() function. Unfortunately however, the operation of getting an ODBC date from a string representation is not trivial and includes several steps and a couple of traps:

1. if the date comes from a form, i.e. most of the time, the programmer should first make sure that the date is valid;
2. Then, before he can call createODBCdate, he must create a date type variable. The date received from the form is not yet a date: it is a string. Many programmers think that createODBCdate will accept strings as a parameter, because it "works". This is simply not true. What happens is that, knowing that the function expects a date, CF will convert the string to a true date type value first, then it will submit it to the function; we are facing again the problem that CF will "guess" the format, and we know that for European dates, it will not work (it will actually work between the 13^st and the last day of each month, then suddenly between the 1^st and the 12^th of the following month, the date will be completely wrong. A true time bomb!
3. One must then create a true date type value to prevent this guess work. A function like createDate() could help, but it needs to have three parameters passed for day, month, year, which is not really handy in that occasion. A function like dateUnformat that would create a date from a string an a mask like for dateFormat() would help, but it deos not exists. There is a function called ParseDateTime in ColdFusion, but again, the function expects the string to contain a valide American date, not handy for applications working with dates in European and ISO format.

The Only Really Safe and Ultimate Solution: CF_convertDate

This simple yet handy tag is able to receive a date in any format specified by the programer, validate it with no guessing and convert it to another format specified by the programer. It validates the date and returns either a valide date in the specified format, or an error message.

It also comes in two flavors: a standard custom tag compatible with all ColdFusion versions, and a function for CF 5+. Both are included in the same package.

NOTE

If you find this custom tag useful, or if you have any suggestion to make it even more useful, just let me know, I'll be glad to enhance it.

INSTALLATION

In order to use this Custom Tag, just store the file convertDate.cfm found in the zip file in the special Custom Tags directory in your ColdFusion server. This directory is generally named \Cfusion\CustomTags. You may also store the file in a directory defined as a path for custom tags in the ColdFusion Administrator (CF 5.0+) or in the same directory as the calling template.

The other files are this documentation (convertDateDoc.cfm) and a small program to test the tag: testConvertDate.cfm; just store them in any convenient place in the HTTP area on your development server.

If you prefer to use the function version, just include the file convertDateFN.cfm in your template, or cut and paste its content into your own program.

CUSTOM TAG VERSION SYNTAX

<CF_convertDate inputVariable = "" inputType = "date|string" outputType = "date|stringODBCdate" inputMask = "" outputMask = "" outputVariable = "" >

TAG	ATTRIBUTE	CONTENT	REQUIRED	DEFAULT
<CF_convertDate	inputVariable=	string containing name of the variable to validate and convert; since the tag can accept not only strings representing a date, but a true date type variable, and since only a string ca be passed in an attribute, this attribute contains the name of a variable containing a date.	Yes
	=inputType	Type of the date transmited in the input variable. It may be either "string" or "date".	No	"string"
	=outputType	Type the date should converted to in the output variable. It may be either "string", "date" or "odbcdate".	No	"odbcdate"
	inputMask=	Mask specifying the format of the date as to be found in the input variable if it is a string; ex: "dd/mm/yyyy" or mm/dd/yyyy. If the part for the year has only two digits, the year will be assumed using the same rule as for the function ParseDateTime (), ie: Year values 0 - 29 are interpreted as 21st century dates. Year values 30 - 99 are interpreted as 20th century dates. "US" and "EU" are accepted as short cuts respectively for "mm/dd/yyyy" and "dd/mm/yyyy". If the variable contains a date of type "date", this attribute is not used.	No	"mm/dd/yyyy"
	outputMask=	Mask specifying the format the date should be converted to. This attribute is used only if outputType="string".	No	"mm/dd/yyyy"
	outputVariable=	Name of a variable in the calling template to receive the result.	No	"outputDate"

ERROR HANDLING

The tag also validates the date submitted in input. If no error is detected, the caller's variable specified in the attribute variable contains the output date. Another variable is also defined in the callers's scope by the tag: dateError will contain an error message, or an empty string if there is no error. One can then check the content of this variable to decide if the program may proceed with the converted date or if some error message should be returned to the user.

FUNCTION VERSION SYNTAX

convertedDtate = convertDate ( inputDate, inputType, outputType, [inputMask], [outputMask])

inputDate	Date to be converted. May be a string like "09/01/2003" or a date like now()
inputType	Type of the date to be converted; may be "string" or "date".
outputType	Type of the returned converted date. Should be "string", "date" or "odbcdate"
inputMask	Mask specifying the format of the date as to be found in the input variable if it is a string; ex: "dd/mm/yyyy" or mm/dd/yyyy. If the part for the year has only two digits, the year will be assumed using the same rule as for the function ParseDateTime (), ie: Year values 0 - 29 are interpreted as 21st century dates. Year values 30 - 99 are interpreted as 20th century dates. "US" and "EU" are accepted as short cuts respectively for "mm/dd/yyyy" and "dd/mm/yyyy". If the variable contains a date of type "date", this parameter is not used.
outputMask	Mask specifying the format the date should be converted to. This attribute is used only if outputType="string".

ERROR HANDLING BY THE FUNCTION

Normaly, the function will return the converted date in a simple value. If an error was encountered, the function returns a structure instead. The structure contains only one element: .message.

After the call to the function, the programer should then test if the returned value is a structure (see examples).

EXAMPLES

Validates a date entered in American format and displays it in full format.
Note that dates like "15/10/2003" which are valid in European format and accepted by the isDate() function, will be refused by convertDate.
Custom tag version:

<CF_convertDate inputVariable="form.myDate"	inputMask="US" outputMask="ddd, mmmm dd, yyyy">
<CFOUTPUT>
<CFIF dateError EQ "">
  The date is #outputDate#
<CFELSE>
  Ooops! Your date is invalid: #dateError#.
</CFIF>
</CFOUTPUT>

Function version:

<CFSET outputDate = convertDate(form.myDate, "string", "string", "EU", "ddd, mmmm dd, yyyy")>
<CFOUTPUT>
<CFIF NOT isStruct(outputDate)>	
	#outputDate#
<CFELSE>
  >Ooops! Your date is invalid: #outputDate.message#.
</CFIF>
</CFOUTPUT>

Validates and converts a date received in a form in European format ("dd/mm/yyyy") to ODBC format in order to insert it in a table:
Custom tag version:

<CF_convertDate inputVariable="form.myDate"	inputMask="EU">
<CFIF dateError EQ "">
  <CFQUERY DATASOURCE="myDatasource">
    INSERT INTO myTable
    VALUES (#outputDate#)
  </CFQUERY>
<CFELSE>
  <CFOUTPUT>Ooops! Your date is invalid: #dateError#.</CFOUTPUT>
</CFIF>

Function version:

<CFSET outputDate = convertDate(form.myDate, "string", "odbcdate", "EU")>
<CFIF NOT isStruct(outputDate)>	
  <CFQUERY DATASOURCE="myDatasource">
    INSERT INTO myTable
    VALUES (#outputDate#)
  </CFQUERY>
<CFELSE>
  <CFOUTPUT>Ooops! Your date is invalid: #outputDate.message#.</CFOUTPUT>
</CFIF>

If the date has been already validated in the client browser, the test for errors can be bypassed, and the function can be used directly in a query. This can be a particularily simple and handy way to manage dates entered in European (aka ISO) format and make sure they are properly converted in ODBC format for the query.

 <CFQUERY DATASOURCE="myDatasource">
   INSERT INTO myTable
   VALUES (#convertDate (form.myDate, "string", "odbcdate", "EU")#)
 </CFQUERY>

Here is a small test program that reads values for the attributes and apply them to the tag and the function as well. If you select an input type of "date" and leave the "Enter a date" field blank, a variable initialized with Now() will be used.

See other cool tags by