Coldfusion UK date format

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
clivecrawdevilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dgrafxCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
code_blokeCommented:
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
clivecrawdevilAuthor 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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

dgrafxCommented:
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
clivecrawdevilAuthor 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
0
dgrafxCommented:
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
clivecrawdevilAuthor 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!!! :-(
0
code_blokeCommented:
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
dgrafxCommented:
>>code bloke
He's doing queries right on the database - not out in CF code.
Where you could then format anyway you want.
0
clivecrawdevilAuthor 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
0
dgrafxCommented:
Oh - hey - if you're paying $$$
contact me via my contact info :)
0
code_blokeCommented:
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
clivecrawdevilAuthor 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
0
dgrafxCommented:
Try not formatting it - just use lsdateformat
0
clivecrawdevilAuthor 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?

0
dgrafxCommented:
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
digicidalCommented:
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
AtisCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.