Baffled by ColdFusion date handling 2

[Rayz66]

Hi there ...

I have a form which has a field for entering a date. Now the date has to be in European format, but I can't figure out how to tell ColdFusion that the date is European when I submit the form to the database.

I'm using CFINSERT/CFUPDATE, and as far as I can tell, ColdFusion assumes the dates are in mm/dd/yyyy format ... unless it can't translate it (for example, 23/07/2004), in which case it takes a guess that this was meant to be European! Is there any way to explicitly tell ColdFusion that it is dealing with European dates when posting the dates to a database (I'm using Access by the way, but I'm pretty sure that the translation is down to ColdFusion).

 

[webmigit]

You can use separate text boxes:

<input type="text" name="ddate"> / 
<input type="text" name="mdate"> / 
<input type="text" name="ydate"><br>
DD / MM / YYYY

And then in your processing

<cfset form.usdate = "#form.mdate#/#form.ddate#/#form.ydate#">

You can let the user enter it in any order and enter it as a usdate...

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[Rayz66]

Aah! So it is something to do with the way ColdFusion handles the dates .... very strange. I guess it's a legacy thing.

Thanks for the help, can't use the three text box solution unfortunately (the design folk won't hear of it), but at least I know I'm looking at a CF quirk rather than a bug in my CFML ....

 

[ecobb]

Actually, CF can make the date look however you want it to look, but each database has it's own particular way that it likes to handle dates. So, why not just give the date to the database, let it format it however it wants, then just have CF display it like you want to see it? Keep the database happy, it runs better that way... ;-)




Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[Rayz66]

Yes, making the date look like EU, was not too tricky, the problem was getting ColdFusion to recognise EU formats when processing the date for the database. If CF sees

08/10/2004

Then it assumes that this is 10th of August, when I really wanted it to be 8th of October. However, when it sees

23/07/2004, it realises that this couldn't be a US date, and so translates it into 07/23/2004.

Not quite what I was expecting....

The easiest thing is just to swap the date/month around before the date is written to the database, or use the third party cf_convertdate tag.

I was just a little surprised that CF seems unable to do that for you; but now I know what's going on, it's no biggie.

 

[webmigit]

The problem you run into is what if the user enters mm/dd.. Like a tourist or something...

In the 3-textbox method above, you can say..

<cfoutput>
<input type="text" name="ddate" value="#day(date_from_db)#"> /
<input type="text" name="mdate" value="#month(date_from_db)#"> /
<input type="text" name="ydate" value="#year(date_from_db)#">
</cfoutput>

Unless the design folk are just being annoying because they want to be, its quite doable... Many many sites make slick use of three text boxes.. and like I said, it does control the entry method, you know how who's entering what.

And if the data is invald (February 30th, for example) you can detect that.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[ecobb]

Don't know if this will help, but if you're running MX you may can use the SetLocale function.
According to the CF Help files it

Sets a country/language locale option for a ColdFusion application user; the setting persists for the current ColdFusion application session. The locale value encapsulates a set of attributes that determine the default display format of date, time, number, and currency values, according to language and regional conventions.

I've never used it, so I don't know much about it, but hopefully it can help you out.




Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[Rayz66]

>> Unless the design folk are just being annoying because they want to be, its quite doable... <<

I couldn't possibly comment on the reasoning behind the designers .... :-/

I like the three box option, but we also have a thirdp party widget that allows for easy date entry; it needs a single text box to work.

You are right though; three boxes would make the error checking easier.

 

[Rayz66]

>> Don't know if this will help, but if you're running MX you may can use the SetLocale function. <<

Right!! That may be what I'm looking for! Thanks!