We help IT Professionals succeed at work.

Coldfusion UK date format

Medium Priority
1,110 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
Comment
Watch Question

CERTIFIED EXPERT
Commented:
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 ...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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)

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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!!! :-(
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.
CERTIFIED EXPERT

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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
Oh - hey - if you're paying $$$
contact me via my contact info :)
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")#

Author

Commented:
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
CERTIFIED EXPERT

Commented:
Try not formatting it - just use lsdateformat

Author

Commented:
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?

CERTIFIED EXPERT

Commented:
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.
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.

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.