Skip to main content
J
Jeremy Tan
Known Participant
April 9, 2008
Question

Date Issue

  • April 9, 2008
  • 2 replies
  • 1229 views
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?
This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
April 13, 2008
lsParseDate is used instead of CreateOdbcDateTime. cfqueryparam is still a good idea since it tends to improve performance.
N
Newsgroup_User
Inspiring
April 10, 2008
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
Prasanth_Kumar_S
Inspiring
April 10, 2008
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
J
Jeremy TanAuthor
Known Participant
April 10, 2008
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...
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices