US date and UK date in a form 1

[Speckly]

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 ColdFusion web application and using Microsoft Access 2000.

The date problem details:

In one form I have created two textboxes that require the user to enter two dates, 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 to 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

 

[NorthStarDA]

i would try wrapping your CreateODBCDate function in a DateFormat like so:

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

 

[ecobb]

#1 Don't worry about how the database stores the dates, let it keep dates however it is set to by default. Let the database be the database, it makes queries, inserts, and updates so much easier.

#2 Format your date displays with CF to look however you want them to. Just use #DateFormat('Value', 'dd/mm/yyyy')# whenever you display the date.

That way, your database is happy, and your users see what you want them to regardless of how it's formatted in the database. (unless they open up the database and look)





Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[NorthStarDA]

props to Ecobb, that is a much better solution =)

 

[Speckly]

Dear NorthStarDA and Ecobb (+ other readers),

Thanks for your reply. I tried NorthStarDA's code but no records came out of the query when there should be some.

The date that users enter is understood by Access as being American, regardless, it seems, of the format I give the date variable to the query. Therefore, Ecobb's solution isn't going to work..I think...if I have understood it as you meant.

If I change the date format to UK date after the query has executed I have the wrong records. I think I'm in a deep black hole? Is there any way out?

Kind Regards,

Cathy

 

[Speckly]

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

 

[ecobb]

So if I understand correctly, your users enter the date in UK format and Access tries to store the date in US format, right? Is Access storing the actual date value correctly (regardless of the format)? If so, you should be ok. Just have CF display all of your dates on the web site in UK format. Even if the date in the database is in US format, CF can easily translate it over into UK format.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[Speckly]

Hi Ecobb,

Yes that's right, for some reason the database needs date variables to be in US format in order to execute a query properly on data held in the database which is format in UK format ( but maybe it's transport in US format??) I must say its mind boggling!

The database is as far as I know storing the date value correctly, it has been operational for 2 years. I'm just using a copy of it for testing.

Thanks for your help... can I ask you another question though...

I've posted a note on another thread about this but, when I download a recordset (resulting from a query) to excel from ColdFusion, the default file extension is .cfm but I need .xls. In my code I specify to download to excel with:

<CFCONTENT TYPE="application/msexcel" FILE="C:\temp\exptemp.xls">

But I still get .cfm extension by default.

Can you help??

Cathy