?
Solved

Coldfusion UK date format

Posted on 2006-03-27
18
Medium Priority
?
1,003 Views
Last Modified: 2013-12-20
Hi Experts

I have a form that inputs information into an Access database and I cannot seem to get it to use the UK date format of DD/MM/YYYY the query looks like this

<cfset SetLocale("English (UK)")>
<cfinclude template="../Login/include_CheckAuthority.cfm">
<cfquery datasource="ILRData" name="Update">
Select *
From tblSite
Where SiteID = #URL.SiteID#
</cfquery>

The form looks like this

<cfform name="Site" method="post" action="SiteAction.cfm?Update" format="flash" width="790" height="400" skin="halosilver" timeout="10">
                        <cfformgroup type="panel" label="Site form">
                        <cfformgroup type="horizontal">
                        <cfformgroup type="vbox">
                        <cfinput type="hidden" name="SiteID" value="#Update.SiteID#">
                        <cfinput type="text" name="SiteName" value="#Update.SiteName#" size="15" label="Site Name" required="yes">
                        <cfinput type="text" name="Address1" value="#Update.Address1#" size="15" label="Address">
                        <cfinput type="text" name="Address2" value="#Update.Address2#" size="15">
                        <cfinput type="text" name="Address3" value="#Update.Address3#" size="15">
                        <cfinput type="text" name="Address4" value="#Update.Address4#" size="15">
                        <cfinput type="text" name="PostCode" value="#Update.PostCode#" size="15" label="Post Code" required="yes">
            </cfformgroup>
            <cfformgroup type="vbox">
                        <cfinput type="datefield" name="DateStarted" value="#LSDateFormat(Update.DateStarted, "dd/mm/yyyy")#" size="15" width="150" label="Date started site" mask="dd/mm/yyyy" validateat="onBlur" validate="eurodate" required="yes" id="DateStarted">
                        <cfinput type="text" name="ContactForename" value="#Update.ContactForename#" size="15" label="Contact Forename">
                        <cfinput type="text" name="ContactSurname" value="#Update.ContactSurname#" size="15" label="Contact Surname">
                        <cfinput type="text" name="Telephone" value="#Update.Telephone#" size="15" label="Contact Tel" required="yes">
                        <cfinput type="text" name="Mobile" value="#Update.Mobile#" size="15" label="Contact Mobile">
                        <cfinput type="text" name="Mail" value="#Update.Mail#" size="15" label="Contact E-Mail">
            </cfformgroup>      
            </cfformgroup>
            <cfformgroup type="horizontal" style="horizontalAlign:right">
                  <!--- Submit and reset buttons --->
                  <cfinput type = "submit" name="submit" width="100" value ="Save" tooltip="Click to Save">
                  <cfinput type = "reset" name="reset" width="100" value = "Reset">
                  </cfformgroup>
            </cfformgroup>
            </cfform>

And the action page loos like this

<cfset SetLocale("English (UK)")>
<cfinclude template="../Login/include_CheckAuthority.cfm">
<cftransaction>
<cfif isDefined("url.Update")>
<cfset Date2 = DateFormat(Form.DateStarted, "dd/mm/yyyy")>
<cfquery name="Update" datasource="ILRData">
UPDATE tblSite
SET tblSite.DateStarted = '#Date2#', tblSite.SiteName = "#Form.SiteName#",
tblSite.Address1 = "#Form.Address1#", tblSite.Address2 = "#Form.Address2#",
tblSite.Address3 = "#Form.Address3#", tblSite.Address4 = "#Form.Address4#",
tblSite.PostCode = "#Form.PostCode#", tblSite.ContactForename = "#Form.ContactForename#",
tblSite.ContactSurname = "#Form.ContactSurname#", tblSite.Telephone = "#Form.Telephone#",
tblSite.Mobile = "#Form.Mobile#", tblSite.Mail = "#Form.Mail#"
WHERE (((tblSite.SiteID)=#Form.SiteID#));
</cfquery>
<cfupdate datasource="ILRData" tablename="tblSite" formfields="SiteID,SiteName,Address1,Address2,Address3,Address4,PostCode,DateStarted,ContactForename,ContactSurname,Telephone,Mobile,Mail">
<cfelse>
<cfset Date2 = CreateODBCDateTime(LSDateFormat(#Form.DateStarted#,'dd/mm/yy'))>
<cfquery name="Insert" datasource="ILRData">
Insert into tblSite
(SiteName,Address1,Address2,Address3,Address4,PostCode,DateStarted,ContactForename,ContactSurname,Telephone,Mobile,Mail,Project,Tutor)
values ('#SiteName#', '#Address1#', '#Address2#', '#Address3#', '#Address4#', '#PostCode#', #Date2#, '#ContactForename#', '#ContactSurname#', '#Telephone#', '#Mobile#', '#Mail#', '#Project#', '#Tutor#')
</cfquery>
</cfif>
</cftransaction>
<cflocation url="../Projects/Redirect.cfm">

Can’t for the life of me work out why this will not work? It keeps changing the date to a US format.

e.g. 03/02/2006 is entered but when you come to update the form 02/03/2006 is returned.

Any help would be greatly appreciated as I have spent hours trying to work this one out and used all different combinations of functions.

Is anyone else having trouble with UK dates in Coldfusion?

Clive
0
Comment
Question by:clivecrawdevil
  • 7
  • 6
  • 3
  • +2
18 Comments
 
LVL 25

Accepted Solution

by:
dgrafx earned 1500 total points
ID: 16300209
Hello again Clive,
So changing format to short date didn't work?
Can you modify code to create a different column for each datepart?
day has a column - month has a column - year has a column

BTW - This isn't a ColdFusion issue - it's an Access issue.

Good Luck ...
0
 
LVL 1

Expert Comment

by:code_bloke
ID: 16300358
Hi Clive,
dGrafx is right - this ins't a CF thing - it's Access.
Best bet is to convert the date into  odbcdate format before saving in access, then use the formatdate() function when you need to display the date.

-code_bloke
0
 

Author Comment

by:clivecrawdevil
ID: 16300448
Thanks for the help do you think it could be when coldfusion is requesting information access thinks its american? Is there a way to set coldfusion to UK as default?

Thanks

Clive
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 25

Expert Comment

by:dgrafx
ID: 16300702
Alter the regional settings of your pc - from control panel.
You must have it set to US - cause I believe all contries use the format you are looking for except US.
(cause we rule the world)
0
 

Author Comment

by:clivecrawdevil
ID: 16300754
Hi dgrafx

Checked that already my PC and the server are in UK for regional options. thats why its so weird the database appears fine but when coldfusion talks to it it goes all screwy. Cant really do the dates in three sections i have dates right through my application and i need to be able to search them.

Clive
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16300826
I had just thought of that.
And I tested it!
I switched to UK and access now displayed dates in uk format - I switched back and now displayed in US format again.
Do you have a little regional "toolbar" off to the right of your taskbar that says for ex UK?
Does it say UK?
Maybe need to reboot after ???
I didn't though - it was immediate and I didn't make any changes to access - just windows.
Try setting to US and then back to UK ???
I use WinXP - and tested on access that came with office XP - whatever version that is.
0
 

Author Comment

by:clivecrawdevil
ID: 16300900
Hi Dgrafx

tried that no joy im affraid!

got my insert working using this

<cfset form.mydate = "#Form.DateStarted#">
<cfset variables.thisyear = ListLast(form.mydate,"/")>
<cfset variables.thismonth = ListGetAt(form.mydate,2,"/")>
<cfset variables.thisday = ListFirst(form.mydate,"/")>
<cfset variables.datedata = createdate(variables.thisyear,variables.thismonth,variables.thisday)>

<cfquery name="Insert" datasource="ILRData">
Insert into tblSite
(SiteName,Address1,Address2,Address3,Address4,PostCode,DateStarted,ContactForename,ContactSurname,Telephone,Mobile,Mail,Project,Tutor)
values ('#SiteName#', '#Address1#', '#Address2#', '#Address3#', '#Address4#', '#PostCode#', <cfqueryparam cfsqltype="cf_sql_date" value="#variables.datedata#">, '#ContactForename#', '#ContactSurname#', '#Telephone#', '#Mobile#', '#Mail#', '#Project#', '#Tutor#')
</cfquery>
</cfif>

but the update changes it back to american. bummer!!! :-(
0
 
LVL 1

Expert Comment

by:code_bloke
ID: 16301463
Hi there,
Before inserting the date into the database use createODBCdate() or use a queryparam which will have the same effect
Obviously you need to modify your update code to do the same thing.

When you display it on the page use dateformat()

Problem solved regardless of how the db is deciding to store it.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16301509
>>code bloke
He's doing queries right on the database - not out in CF code.
Where you could then format anyway you want.
0
 

Author Comment

by:clivecrawdevil
ID: 16301550
Hi Code Bloke

I have done what you suggested already this is my insert

<cfset form.mydate = "#Form.DateStarted#">
<cfset variables.thisyear = ListLast(form.mydate,"/")>
<cfset variables.thismonth = ListGetAt(form.mydate,2,"/")>
<cfset variables.thisday = ListFirst(form.mydate,"/")>
<cfset variables.datedata = createdate(variables.thisyear,variables.thismonth,variables.thisday)>

<cfquery name="Insert" datasource="ILRData">
Insert into tblSite
(SiteName,Address1,Address2,Address3,Address4,PostCode,DateStarted,ContactForename,ContactSurname,Telephone,Mobile,Mail,Project,Tutor)
values ('#SiteName#', '#Address1#', '#Address2#', '#Address3#', '#Address4#', '#PostCode#', <cfqueryparam cfsqltype="cf_sql_date" value="#variables.datedata#">, '#ContactForename#', '#ContactSurname#', '#Telephone#', '#Mobile#', '#Mail#', '#Project#', '#Tutor#')
</cfquery>

This is my update

<cfset form.mydate = "#Form.DateStarted#">
<cfset variables.thisyear = ListLast(form.mydate,"/")>
<cfset variables.thismonth = ListGetAt(form.mydate,2,"/")>
<cfset variables.thisday = ListFirst(form.mydate,"/")>
<cfset variables.datedata = createdate(variables.thisyear,variables.thismonth,variables.thisday)>
<cfquery name="Update" datasource="ILRData">
UPDATE tblSite
SET tblSite.DateStarted = <cfqueryparam cfsqltype="cf_sql_date" value="#variables.datedata#">, tblSite.SiteName = "#Form.SiteName#",
tblSite.Address1 = "#Form.Address1#", tblSite.Address2 = "#Form.Address2#",
tblSite.Address3 = "#Form.Address3#", tblSite.Address4 = "#Form.Address4#",
tblSite.PostCode = "#Form.PostCode#", tblSite.ContactForename = "#Form.ContactForename#",
tblSite.ContactSurname = "#Form.ContactSurname#", tblSite.Telephone = "#Form.Telephone#",
tblSite.Mobile = "#Form.Mobile#", tblSite.Mail = "#Form.Mail#"
WHERE (((tblSite.SiteID)=#Form.SiteID#));
</cfquery>

And this is the field i am using to display the date

<cfinput type="datefield" name="DateStarted" value="#LSDateFormat(CreateODBCDate(Update.DateStarted), "dd/mm/yyyy")#" size="15" width="150" label="Date started site" mask="dd/mm/yyyy" validateat="onBlur" validate="eurodate" required="yes" id="DateStarted">

Can't see what is wrong? It's driving me mental been trying everyway I can find to get it fixed. Willing to pay good money for a solution.

Clive
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16301574
Oh - hey - if you're paying $$$
contact me via my contact info :)
0
 
LVL 1

Expert Comment

by:code_bloke
ID: 16301591
Are you sure you need to use the <cfinput> controls.
I noticed you have halo blue style applied.

However for the value in the cfinput dont try and format that - leave it as raw from the query ie.
<cfinput type="datefield" name="DateStarted" value="#Update.DateStarted#" size="15" width="150" label="Date started site" mask="dd/mm/yyyy" validateat="onBlur" validate="eurodate" required="yes" id="DateStarted">

However try just outputing the date as text #dateformat(Update.DateStarted,"dd-mmm-yyyy")#
0
 

Author Comment

by:clivecrawdevil
ID: 16301603
Be happy to get just he site working at the moment dgrafx, if i put 10/01/2006 in then when i come to update the form i have 01/10/2006 which causes the obvious problems. I’m formatting the date on the way in and on the way back out. Just cannot work out where its going wrong.

Clive
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16301626
Try not formatting it - just use lsdateformat
0
 

Author Comment

by:clivecrawdevil
ID: 16301692
Tried that still get the same problem! Its happening on my local testing machine and on my server. Don't unerstand why? Is it my code? Is it the database? Is it coldfusion?

0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16301746
At this point ...
I believe it's "something" outside of what you've posted here.
I'm sorry that I can't see whats up.
0
 
LVL 9

Expert Comment

by:digicidal
ID: 16307976
Although it's not really a problem have you used GetLocale() to view what the current Locale setting for the page is?  Also, you could do this in one shot since SetLocale() will return the previous setting:

<cfset OrigLocale = setLocale('English (UK)')>
<cfoutput>Original Locale Was: #OrigLocale#</cfoutput>

That would at least verify that you are indeed having a problem with the JVM or Flash Player transmitting incorrect homing information to the server.
Also check in the docs for more info on how to 'hardset' your ColdFusion server to default to a particular locale:

http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00001202.htm

In particular this line: You can set JVM locale value explicitly in ColdFusion MX in the ColdFusion Administrator Java and JVM Settings page JVM Arguments field; for example:
-Duser.language=de -Duser.region=DE.
0
 
LVL 1

Expert Comment

by:Atis
ID: 16310490
As of my personal experience, you have to work in two layers: one ir your application and another is your database.
While you can easily change first one (changing locales, etc.) to get your specific environment you often can't do anything about second - database.

To put it short:
- when inserting date use US style MM/DD/YYYY (always)
- when it's retrieved from DB use DateFormat to format your ODBC/Object style date to one you need (
- don't use Access DB for production purposes, it's desktop DB by design (see Microsoft's comments) ... or at least avoid.

Long since I'm not using Access DB (SQL 2000 now), so, while there could be more locale specific things involved using Access DB, above mentioned "formula" works for me (I have to converto to "dd.mm.yyyy").

If this doesn't work ... please specify your environment (CF server, ODBC driver type used, regional settings, DB file version (pre-2000 for example or not) others you find relevant). I could try to recreate your problem.

Atis
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question