Date Issue

[Jeremy_Tan]

I am not sure is this the right forum. Anyway...

I am from New Zealand, in here we use DD/MM/YYYY. I notice when i try to write a date string to a database using either the createODBCDateTime or the cfqueryparam with cfsqltype = cf_sql_timestamp, there is very small chance (very randomly) that the output end up in MM/DD/YYYY.

For example.
1/4/2009 will become 4 Jan 2009, but
1/2/2009 will become 1 Feb 2009,

This doesn't every time.

Anyone know what is the cause of it? and the solution?

[Newsgroup_User]

Jeremy Tan wrote:
> I am from New Zealand, in here we use DD/MM/YYYY. I notice when i try to write
> a date string to a database using either the createODBCDateTime or the

first off not a good idea to store your datetimes as strings.

> cfqueryparam with cfsqltype = cf_sql_timestamp, there is very small chance
> (very randomly) that the output end up in MM/DD/YYYY.
>
> For example.
> 1/4/2009 will become 4 Jan 2009, but
> 1/2/2009 will become 1 Feb 2009,

my WAG would be from user input and/or not validating the input. where are the
date data coming from?

[Prasanth_Kumar_S]

Hi,
One reason is that your web server has an American format regional settings. This can lead to CF interpreting date in mm/dd/yyyy format. Also, in some cases like 01/02/08, system will confuse with January 2nd and February 1st. You can use some date pickers for user date input.

-Prasanth

[Jeremy_Tan]

I am very sure the server configuration is set the New Zealand region with DD/MM/YYYY.

I didn't store the date as string, I store it as a date/time data type, but i pass in the input as a string to the createODBCDatetime and the cfqueryparam.

The date is input by using date picker. so there is not user input here. user must select the date from the date picker.

here is an example.
We have a form text field call dtStart, this field is populated by a date picker. When the user click on a date, the date picker will populate the date in DD/MM/YYYY, for example, 1 April 2008, will become 01/04/2008.

After the user submit, and i output the text field to the screen, which show 01/04/2008. Now here is the problems.
if i try to use the #createODBCDateTime(FORM.dtStart)# to output it to the screen and insert to the database, or use the cfqueryparam to insert to the databas. There is a random chance that it will show up as 4 January 2008 on the screen and in the database.

If i try again by trying to correct it by reformat date string using createDate function during the second try, because the createDate is returning a date object, and i pass this time object to either the createODBCDateTime or cfqueryparam, then it will show up as 1 April 2008.

Now, i try the third time, this time i didn't use the createDate function to convert the date string to date object. I pass the date string directly to the createodbcDate and cfqueryparam. Then it will show up as 1 April 2008 (instead of 4 January 2008 in the first try).

Please note, all 3 tries are happen in same session.


As i mention before, this problem doesn't happen very often, and doesn't happen on the same date too. For example, if i try this example again in few weeks later. the problem may never happen again...

[Newsgroup_User]

Jeremy Tan wrote:
> I am very sure the server configuration is set the New Zealand region with
> DD/MM/YYYY.

what does this say?
<cfoutput>#getLocale()#</cfoutput>

> if i try to use the #createODBCDateTime(FORM.dtStart)# to output it to the
> screen and insert to the database, or use the cfqueryparam to insert to the
> databas. There is a random chance that it will show up as 4 January 2008 either

if your date string is "01/04/2008" (dd/mm/yyyy) then there's a 100% chance it
will be created as 4-jan-2008 using createODBCDateTime(). *all* of cf's non-LS
methods use en_US as their locale where the date format is month-day-year. so
this is happening *all* of the time. the *only* way to make it swallow your date
format is make it un-ambiguous, something like "1-April-2008" which will never
get mis-interpreted & turned into any other date. otherwise use createDate() &
pull the date parts out of your datepicker's data.

> As i mention before, this problem doesn't happen very often, and doesn't
> happen on the same date too. For example, if i try this example again in few
> weeks later. the problem may never happen again...

no, it happens *all* the time if you use ambiguous date strings like that.

[Jeremy_Tan]

Thansk for your reply Paul.

Is that maen both createODBCDateTime() and <cfqueryparam cfsqltype="cf_sql_date"> always return the en_US format?

We try not go to down to the path which we need to recreate the date object by use createDate() &
pull the date parts out from the datepicker's data.

[Newsgroup_User]

Jeremy Tan wrote:
> Thansk for Paul.
>
> Is that maen both createODBCDateTime() and <cfqueryparam
> cfsqltype="cf_sql_date"> always return the en_US format?

you got it backwards, createODBCDateTime() expects en_US locale date data
(month-day-year) as input. you're feeding it en_NZ locale date data
(day-month-year). it creates a datetime object based on what it's given.

another approach would be to use:

setLocale("en_NZ")

in your application cfm/cfc then when you want to use your en_NZ dates use the
LS method:

lsParseDateTime()

or you can set the locale in the method if you're on cf8.

[Jeremy_Tan]

Thanks again paul.

So, that is that i can use lsParseDate to insert/update date object to database? I don't really need to use the createODBCDateTime() and <cfqueryparam cfsqltype="cf_sql_date">?

[Newsgroup_User]

Jeremy Tan wrote:
> So, that is that i can use lsParseDate to insert/update date object to
> database? I don't really need to use the createODBCDateTime() and
> <cfqueryparam cfsqltype="cf_sql_date">?

no, you pretty much should always use cfqueryparam w/public facing websites.

[Dan_Bracuk]

lsParseDate is used instead of CreateOdbcDateTime. cfqueryparam is still a good idea since it tends to improve performance.