Skip to main content
J
jjdonate
Known Participant
June 14, 2013
Answered

CF, french dates and MS Access

  • June 14, 2013
  • 1 reply
  • 1364 views

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
This topic has been closed for replies.
Correct answer BKBK
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">
1 reply
BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
June 15, 2013
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">
J
jjdonateAuthor
Known Participant
June 16, 2013
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
J
jjdonateAuthor
Known Participant
June 17, 2013
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

        
                                                

        

    


            

                                        

            
                        

                                                                                                            
            







        

                    

    
    





    


                                        

                                                        

            
        
    






                                    
Terms & ConditionsAccessibility statement

            
        



        
            
    
        
    
        



        
        
        


        


        
        
        
        
                                                        

                            
    

                    

  

    

      
        
      
    

    

      
        Using the community
      
      
        Terms of use
      
      
        Privacy
      
      
        Cookie preferences
      
      
        Do not sell or share my personal information
      
      
        ad choicesAdChoices