CF, french dates and MS Access

[jjdonate]

Hi,

I have a problem inserting dates in a MS Access database. Days and month are swapped everytime the date stay valid.

Ex: 05 of june 2013 become six of may 2013 but 13th of june is not changed.

I use:

#CreateODBCDate(Dateformat(mydate,"dd/mm/yyyy"))# 

Of course i put in my Application.cfm :

<cfset Locale=setLocale("French (standard)")>

I tried too using a variable :

<cfset variables.MyDate="##" & "#Dateformat(form.MyFormDate,"dd/mm/yyyy")#" & "##">

and then

Insert into myTable 
(MyDateField)
Values
(#variables.MyDate#)
Where etc...

but the result is the same.

Windows 2003 server and CF8.

Sorry for my terrible english and thanks in advance.


Jean-Jacques

[BKBK · Accepted solution]

The first argument of dateFormat should be a date object. So, start by converting the input string into a date object.

<cfset variables.dateObject = parseDatetime(form.MyFormDate)>

<cfset variables.dateString = dateformat(dateObject,"dd/mm/yyyy")>

It is also advisable to use cfqueryparam when inserting user-input into the database. It ensures parameter binding and security. I am assuming you are inserting the date as a string.

Insert into myTable

(MyDateField)

Values

<cfqueryparam  value="#variables.dateString#" cfsqltype="cf_sql_varchar">

Where etc...

To insert a date, replace the cfqueryparam line with something like

<cfqueryparam  value="#parseDatetime(variables.dateString)#" cfsqltype="cf_sql_date">

[jjdonate]

Thank you very much for your answer,

I will try using parseDateTime ( or LSParseDateTime) and cfqueryparam for insertion.

I will tel you if it resolve my problem.

Best regards,

Jean-Jacques

[jjdonate]

Hi,

@BKBK

I tried with :

<cfset variables.dateObject = parseDatetime(form.MyFormDate)>   (LSParseDateTime doesn't work)

and then

<cfqueryparam  value=#variables.dateObject# cfsqltype="cf_sql_date">

and it works perfectly and no more false interpretation of date with day under 13.

A great thank you for resolving my problem

Best regards,

Jean-Jacques