Notes from the field on ColdFusion (or related) technical issues.

Tuesday, June 01, 2010

CreateODBCDate Retains Time Data

Had an interesting "WTF moment" over the weekend. I tried to prevent a batch job from running on Memorial Day by using the following code:


<cfif dateCompare(createOBDCDate(now()), "May 31, 2010") is 0>
<cfabort />
</cfif>


But this failed to stop the job from running..? So I investigated further:


<cfoutput>
<cfset today = createOBDCDate(now()) />
#today#<br />
#createODBCDate("May 31, 2010")#<br />
#datecompare(today, "May 31, 2010")#
</cfoutput>


Which produced:


{d '2010-05-31'}
{d '2010-05-31'}
1


Okay... so those dates are different. WTF?


<cfoutput>
<cfset today = createOBDCDate(now()) />
#today#<br />
#timeformat(today)#<br />
#createODBCDate("May 31, 2010")#<br />
#datecompare(today, createODBCDate("May 31, 2010"))#
</cfoutput>


This returned:


{d '2010-05-31'}
12:11 PM
{d '2010-05-31'}
1


So... while CreateODBCDate() returns a formatted SQL date-only string, it invisibly retains the time information. Wow.

I wound up doing it the hard way:


<cfset today = createDate(year(now()), month(now()), day(now()))>


<sigh />

9 comments:

  1. Use dateFormat() instead.

    ReplyDelete
  2. Not sure why I should expect dateformat(), a function that converts a date to a string, to work better than createODBCDate(), a function that converts a date to a string. Which is why I brute-forced it using createDate().

    ReplyDelete
  3. I would have written it like so:

    DateCompare(now(), CreateDate(2010, 5, 31))

    CreateODBCDate() is really just meant to format dates for SQL statements.

    ReplyDelete
  4. Russ,

    That wouldn't work either, because now() has a time component. In ColdFusion, all dates that supposedly don't have a time component are simply set to midnight of that date. So, #DateCompare(now(), CreateDate(2010, 5, 31))# would return 1 for the entire day, except for the stroke of midnight at the beginning of the day. (Try it with today's date, and you'll see what I mean.)

    ReplyDelete
  5. JD was right, try this:
    dateformat(now(),"mmmm dd, yyyy") eq "May 31, 2010"

    "Not sure why I should expect dateformat(), a function that converts a date to a string, to work better than createODBCDate(), a function that converts a date to a string" .. Well, because as you pointed out in your post createODBCDate() creates a date time object.

    ReplyDelete
  6. Timothy,

    I didn't say that what JD offered wouldn't work, I'm saying that it'd work for the same reason I'd expect createODBCDate() to work. And if dateFormat() is subtly changed in a future version to work the same way createODBCDate() does, my stuff will break. So, instead of relying on implicit date-to-string-to-date conversions, I've switched to explicitly creating a date-only object via integers, which will be immune to this sort of problem.

    Once bitten, twice shy...

    ReplyDelete
  7. A couple of thoughts here.

    The most obvious (?) one is that dateCompare() dates a third argument which sets the precision of the comparison. By default it does it to the second, whereas you probably only want it to the day. That would - I imagine - have solved your problem.

    Second, you should really not pass strings to date functions. EG: "May 31, 2010" is a string; createOdbcDate() expects a date. CF will cast the string to a date, and do a reasonable job, but it's better to pass a function the data type it expects (IMO).

    That said, createOdbcDate() is - as someone pointed out - only really intended for converting a CF date to an ODBC date, ready for passing as a parameter to an ODBC database driver, so not really appropriate here.

    One thing that might be missing here is that createOdbcDate() does *not* return a string (as you suggest): it returns a coldfusion.runtime.OleDate, which *does* internally store the time component (as you found out). However when one treats a coldfusion.runtime.OleDate as a string (like when outputting it, or passing one hard-coded into an SQL string in a CFQUERY tag), CF calls the OleDate's toString() function to return a string... which is what you're seeing when you output it.

    What's happening kinda makes sense when one stops to think about what's actually going on.

    --
    Adam

    ReplyDelete
  8. PS: why do I have to login to one of those "profiles" (none of which I had...) to be able to post a comment to your blog? What's wrong with just asking for an email address? Or is this a vagary of blogspot?

    --
    Adam

    ReplyDelete
  9. Adam,

    The batch process in question doesn't run at exactly midnight, so using a time resolution of one day would likely have had some unintended results, esp. since the docs aren't clear about rounding behavior.

    I do understand that I was being a lil, um, "creative" in using CreateODBCDate, but I figured that and ODBC date expression is considered to be midnight of that date when used in date+time comparisons, so I expected it to evaluate to midnight. I chose an ODBC format because now() returns an ODBC format, so that seems to be the "native" ColdFusion format for date expressions. It all seemed very logical to me, and I was quite surprised by the result. Hopefully, this post will prevent at least one other person from making the same mistake.

    ~~~

    I turned off anonymous posting because I got tired of having to monitor for spam. It's a setting in BlogSpot. I figure that if someone doesn't already have at least one of those accounts, it's unlikely (tho clearly not impossible) to be someone that reads a lot of technical posts. Sorry for the inconvenience.

    ReplyDelete