Date processing with Access in CF

Some folks have had problems doing date processing within SQL against an Access database. (Let's not get into whether anyone should be choosing Access as a DB. For some low volume sites, it works adequately.)

Part of the date handling problem stems from the fact that Access may need help determining that what you're passing it is a date (versus a number). There are two ways to do this. You could use the CreateODBCDate() function to convert a date to a format that will be passed from CF to SQL as an ODBC Formatted date.

Another is to take advantage of the fact that Access can use pound signs around a date to indicate that the value is a date. Of course, those are the same pound signs that CF uses for variables, which can complicate things.

First, just know that one way to format a SQL statement WITHIN Access to process, for instance, records between two dates (leave CF out of the picture for a moment) would be to render it as:

SELECT StartDate FROM Employee WHERE StartDate between #01/01/97# and #01/01/98#

If you wanted to do that within a CFQUERY statement, then you'd need to escape those pound signs (double them) so that CF didn't think you were trying to refer to variables, as in:

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ##01/01/98##
</CFQUERY>

Of course, you could also convert this to use the ODBCDateFormat function, as in:

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between #ODBCDateFormat("01/01/97")# and #ODBCDateFormat("01/01/98")#
</CFQUERY>

Notice again that the use of this function doesn't require the use of escaped pound signs because the function creates the date in a format Access understands. But you'll usually have the date coming in as a variable. You could then easily change the ODBCDateFormat as in:

<cfset date="01/01/98">
<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between #ODBCDateFormat("01/01/97")# and #ODBCDateFormat(date)#
</CFQUERY>

But if you tried to use the Access form of passing pound-delimited dates, such as in the first two examples above, and you wanted to use a variable for one of them, you'd then need 3 pound signs around that (two for the escaped pound for Access and one for the pound needed to refer to the variable/function), as in:

<cfset date="01/01/98">
<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ###date###
</CFQUERY>

Finally, note that if you wanted to use the now() function in that last example for the date (which returns the current date AND time), or if your incoming "date" variable had minutes and seconds in it, you'd need to wrap it in a dateformat function before using the "pound sign" approach to date formatting in Access because that doesn't like anything but a date to be passed in, as in:.

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ###dateformat(now())###
</CFQUERY>

Note, too, that I've not bothered with any date formatting "mask". In my testing, Access is happy with the default dateformat form of date layout.

So when should you use the pound sign approach? Well, really, it's more something that's needed within Access itself to be able to detect that a string of numbers (and dashes or slashes) are in fact representing a date. The thing is, with the ODBCDateFormat function (and CF's Now() returning a date in ODBCDateTime Format), it's perhaps not as important to use the pound-surrounded approach to dates in Access.