Search code examples
coldfusioncoldfusion-2021

Coldfusion 2021 Date value passed to date function createDateTime is unspecified or invalid - Specify a valid date in createDateTime function

<cfscript>
    SetTimeZone("Asia/Karachi"); //same as our windows server timezone
</cfscript>

<cfoutput> 
  <cfset mydate = 'June 01, 2008'> 
  <cfset JobStartDate=CreateODBCDateTime(mydate)>
  #JobStartDate#
</cfoutput>

Error: Date value passed to date function createDateTime is unspecified or invalid. Specify a valid date in createDateTime function.

I am using ColdFusion 2021 (update 4) on windows server. Under JVM details Java Default Locale is en_US.

Error can reproduced on: cffiddle.org

Would work just fine with other dates for e.g. July 01, 2008 (okay), May 15, 2009 (okay) etc. But shows error with June 01, 2008 (error) and April 07, 2002 (error). Not sure if there might be other dates.

Additional note: Can this issue be associated with the daylight saving in Pakistan?

Daylight Saving Revival

In 2008 Pakistan used daylight saving time for the first time since 2002 to address its energy crisis. The clocks moved one hour ahead (to UTC+6) at midnight between May 31 and June 1 in 2008. The need for daylight saving time during the peak summer season in Pakistan came in light of the country’s struggle for an approximate 4000-megawatt power shortfall. (reference)[https://www.timeanddate.com/news/time/pakistan-extends-dst-2008.html]

Any help would be greatly appreciated. Thanks

Solution

  • I'm afraid the (originally ~) accepted answer to this isn't correct. The problem is entirely down to the daylight savings issue the original poster mentioned.

    The original code is this:

    <cfset mydate = 'June 01, 2008'> 
<cfset JobStartDate=CreateODBCDateTime(mydate)>

    As mentioned, CreateODBCDateTime expects a date/time object, not a string, so the first thing CF needs to do is to convert 'June 01, 2008' to date/time, so the equivalent of this:

    <cfset mydate = createDateTime(2008,6,1,0,0,0)>

    I've added the hour, minute and seconds part there because they are necessary to create a date/time object. You've given it no time part, so CF has to assume zeros there.

    And guess what? on June 1 2008 under the daylight savings rules in Pakistan, there is no such thing as 00:00:00. At the stroke of midnight, time shunted forward to 01:00:00. Hence the error:

    Date value passed to date function createDateTime is unspecified or invalid

    It's telling you exactly what the problem is. One will always get this when trying to use a time that doesn't exist due to daylight savings vagaries. It's exactly the same situation as if one tried to specify the date part as "Feb 32" or something more obviously invalid.

    One will get the same error on 2009-04-15 for the same reason: that's when daylight saving started that year.

    This demonstrates why servers should always be set to UTC. There are very seldom "unexpected" gaps in the time (the odd corrective leap-second notwithstanding), so these problems simply don't arise. If you use UTC and then adjust the timezone for display for humans when necessary, CF will always get it right.

    Another point. Saying that code worked fine in older versions of CF is incorrect (this came up in comments to the earlier answer). SetTimeZone was only added to CFML on ColdFusion for CF2021, and the code in the question errors on earlier versions. So whatever you were or were not experiencing / testing with on older versions of CF was not this issue.