US Dates and UK Dates in ColdFusion form

Dear reader,

I was wondering if you could help me with a problem that I haven't been able to solve despite lots of Web searching.

I am desiging a CF web application and using a Microsoft Access 2000 db.

A date problem:

In one form I have created two textboxes that require the user to enter two dated, a startdate and an enddate. The user input is validated by eurodate. i.e.

cfinput type="text" required="no" validate="eurodate" name="StartDate">


These two form variables are submitted into an action template. These variables are then used as conditions in a query.i.e.

<cfquery name="export_selected_records" datasource="supporting_peopleTESTDB">
SELECT *
FROM GENERAL_NEEDS_CLEAN_test
WHERE ManagingAssoc = '#CLIENT.id#' and LetDate BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#
ORDER BY TenancyType ASC
</cfquery>

I have set the date format in Access to dd/mm/yyyy for the LetDate variable for the query.

But my problem is that when I query the database using the CF form I get US date recordsets in return. I.e. if I type in the textbox startdate 01/06/2004 a UK date, the recordset from the query are selected by US date (06/01/2004). This is not what I want...I need UK date.

I can't understand where in the system the date is set to US date. I have checked Access, and apparently date data are stored as serial numbers regardless of the date format that is chosen by the user. I am stuck. Is the problem in my code in CF or in the set up of the database?

Please can you help me.


Thanks

Not sure where the problem is. But remember that you can always use CF to format the date however you want to after the query is returned using dateFormat( myQuery.dateField, 'myDateMask' ).

Dear readers,

Just to let you know that I fixed the problem by using LSParseDateTime() function. See below for code:

<cfset CreateODBCDate(form.startdate)>
<cfset CreateODBCDate(form.enddate)>

<cfquery name="export_selected_records" datasource="supporting_peopleTESTDB">
SELECT *
FROM GENERAL_NEEDS_CLEAN_test
WHERE ManagingAssoc = '#CLIENT.id#' and LetDate BETWEEN #LSParseDateTime(form.StartDate)#
AND #LSParseDateTime(form.EndDate)#
ORDER BY LetDate ASC
</cfquery>


Just to let people know if anyone else has the same problem.

Cheers