3
<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

Mike Striker
  • 94
  • 7
  • That is weird! I notice that adding a time like 1:00:00 AM allows it to resolve. This feels worthy of reporting as a bug. – davidj May 23 '22 at 12:33
  • @davidj Yes, we have opened this as a bug with Adobe: https://tracker.adobe.com/#/view/CF-4213572 – Mike Striker May 27 '22 at 11:19

2 Answers2

2

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.

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • As the poster of the accepted answer I want to thank you for the great clarification. I wanted to dig more into this issue, but hadn't the time. Going to update my answer and clarify and make a reference to your answer. Is it, somehow, possible to change the accepted answer to yours? However, great to see you again posting to the cfml community again. – AndreasRu Jun 02 '22 at 19:07
  • I am changing the answer to Adam's answer as it seems more promising :) About the method CreateODBCDateTime - would it make sense if CF automatically default the time part to 01:00:00 instead of 00:00:00 when the daylight saving hits for the given timezone? --- About SetTimeZone, our code didn't had the following part in the CF10/11 & worked just fine: SetTimeZone("Asia/Karachi"); We included it just to help reproduce the error. Since our servers timezone defaults to "Asia/Karachi" for Pakistan, so i assume the "SetTimeZone" comes from the server. – Mike Striker Jun 03 '22 at 06:24
  • @MikeStriker I've just upated my answer, recommending Adams. Damn, what a good post from Adam! I'll keep my anser online to keep it as a record. Love cfml and thx Adam for the great elaboration. – AndreasRu Jun 03 '22 at 09:02
0

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.

AndreasRu
  • 1,053
  • 8
  • 14
  • 1
    The question includes the phrase, `Would work just fine with other dates for e.g. July 01, 2008 (okay), May 15, 2009 (okay) etc.`. That appears to make this well written answer incorect. – Dan Bracuk May 23 '22 at 19:20
  • @AndreasRu - I was thinking the same thing, but playing around with the example showed the problem IS due to the locale. There's something really messed up about Adobe's handling of CreateODBCDateTime() and ParseDateTime() :-/ Notice it works if you change the Locale to America/Chicago, etc..? [Example](https://trycf.com/gist/a6b9f103bcbcdbf92fb24ea3b0434494/acf2021?theme=monokai). Those functions should *always* parse strings "... according to the English (U.S.) locale conventions..." - but they don't with CF2021. So that alone is some kind of bug IMO. (cont'd) – SOS May 23 '22 at 19:56
  • 1
    (Update) Oops, that should be *"The (ParseDateTime)[https://cfdocs.org/parsedatetime] function should always parse strings according to the English (U.S.) locale conventions..."*. Anyway, having said all that I agree about CreateODBCDateTime expecting a date object, not a string. CF obviously lets us be lazy and pass in a date string instead, and somewhere in the conversion things go awry. So again agreed LSParseDateTime() is preferred *with* a proper locale to reduce the likelihood of issues i.e. `LSParseDateTime(myDate, "en_us")` .. and possibly a mask if the format is fixed. – SOS May 24 '22 at 01:04
  • @DanBracuk Thx, you are right. I've provided more information about it. – AndreasRu May 24 '22 at 07:10
  • @SOS Thx for the comments. I didn't have the time to do deeper test, but I've provided more information about it. Hope the answer is a bit better. – AndreasRu May 24 '22 at 07:14
  • This function has been used a lot in our code and run in Pakistan time zone. Moreover, in the previous version of CF it works fine but in CF 2021 it throws error with Pakistan Time Zone – Nisar Ahmad May 24 '22 at 07:44
  • @NisarAhmad good point. That should be posted to ACF bug tracker then, so they can look into it and patch it as a regression. – AndreasRu May 24 '22 at 14:26
  • @AndreasRu Still believe this issue may be associated with the daylight saving in Pakistan and the bug is seen in CF 2021. So we opened a new bug with Adobe CF team: [https://tracker.adobe.com/#/view/CF-4213572] (https://tracker.adobe.com/#/view/CF-4213572) In the meantime since the ` lsParseDateTime() ` works for us. Will be marking your answer as partially helpful/correct. Thank you for taking the time to look into this. – Mike Striker May 27 '22 at 11:58
  • Thx @MikeStriker for the accepted answer. I'll look more deeply into this soon and also keep an eye on your filed bug and update my answer as soon as we get more information. Pls ping me if you get some infos about it. – AndreasRu May 27 '22 at 13:58
  • @MikeStriker - `in the previous version of CF it works fine` It was just luck that older versions interpreted the string exactly the way you expected. The documentation says it expects a date. The expected behavior when a date string is passed in is *undefined*. By using a string, you knowingly leave the interpretation up to CF and are stuck with whatever it returns. The breaking change/bug in the latest version illustrates why it's never a good idea to rely on CF to "auto-magically" interpret date strings. (cont'd) – SOS May 28 '22 at 17:10
  • The only way to guarantee a specific result is to parse the string yourself or utilize functions that are documented to accept a date string and mask, to control how the string is interpreted. For example, `ParseDateTime(string, mask)`, or `LSParseDateTime(string, locale, mask)` if working with different locales. – SOS May 28 '22 at 17:11
  • @SOS Yes, that's true the function CreateODBCDateTime expects a date object. But you know the great thing about ColdFusion that we all adore, is the fact that it makes hard things easy for coders. So this bar being set and this working fine in previous versions - we do expect/hope it to keep on working in the future versions too. – Mike Striker May 31 '22 at 04:17
  • @MikeStriker - "Easy" always comes at a cost ;-) Relying solely on CF's to correctly "guess" the expected result makes the code is more vulnerable to unexpected changes or interpretations, such as CreateODBCDateTime("5"). Which is exactly what happened IMO. The code wouldn't have been impacted had it specified how to interpret the string, rather than relying on undefined behavior and auto-magic conversion. I do think there is also some sort of parsing bug involved here, but that's a separate issue. BTW, what's the reason for still using CreateODBCDateTime instead of standard date functions? – SOS May 31 '22 at 22:37
  • @DanBracuk's initial comment made me pay attn to this. I'm afraid this answer is incorrect, despite having a bunch of good information in it. The answer *seems* correct because in the example they've used for `createDateTime`, they have used a different date that is not susceptible to the problem (they've used May 1, not Jun 1). Had they used the date in question, their code would also error in the same way. I've posted a correct answer that explains what is going on. – Adam Cameron Jun 02 '22 at 15:46
  • I've updated my answer correcting my wrong assumptions and making reference to Adams correct answer. – AndreasRu Jun 03 '22 at 09:09