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

Question

<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

Answer 1

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.

Answer 2

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

No, this issue isn't associated with the daylight saving in Pakistan.

Update: As Adam Camaron correctly mentioned...

The problem is entirely down to the daylight savings issue the original poster mentioned.

My initial findings turned out to be incorrect. Please read Adam Camerons very interesting answer to this issue for further deep explanation. His answer should be the accepted one.

I'll keep my answer post active for further reference, because it might be helpfull giving a better overview about similar issues dealing with dateTime objects in CFML.

===========================

Orignal answer:

According to the cfml documentation about CreateODBCDateTime() you need to pass a dateTime-Object as an argument to CreateODBCDateTime(). Your variable mydate = 'June 01, 2008' is just a string that represents a date, but it's not a dateTimeobject.

July 01, 2008 (okay), May 15, 2009 (okay) etc. But shows error with June 01, 2008 (error) and April 07, 2002 (error)

The cfml engine will try somehow to deal with the submitted string and cast it to the correct data type, but this may or may not work: It's simply isn't offically supported, so I'd rather not do it that way.

To create a dateTimeObject you need to parse that string to a dateObject first, e.g. using lsParseDateTime(), and like @sos correctly commented, if you are using different locales, better to always pass the correct locale that the string content represents as an attribute:

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

If your dateTime data is suitable, an alternative would be creating a dateTimeObject from scratch by using createDateTime() function first, e.g.:

<cfoutput> 
  <cfset mydate = createDateTime(2008,5,1,0,0,0)> 
  <cfset JobStartDate=CreateODBCDateTime( mydate )>
  #JobStartDate#
</cfoutput>

Regarding timeshifts across the world, it depends how you are getting and saving your time data and to whom in the world you are delivering it to. In worldwide environments I'd usually save dates to UTCs and output it accordingly by using TimeZone functions.

Side note... because this is commonly missunderstood so I'm posting it here just for posterity: "locales" adapts the string to typical readable (traduced) strings, as they are commonly read and identified by the respective cultures, but they don't change timeZones.

To understand that a little more, I can warmly recommend watching this video about timeZones from Lucee. It's not from ColdFusion but it explains a lot about time internationalization and timezones in CFML and some of it pitfalls.