<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 />
Posts
Use dateFormat() instead.
ReplyDeleteNot 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().
ReplyDeleteI would have written it like so:
ReplyDeleteDateCompare(now(), CreateDate(2010, 5, 31))
CreateODBCDate() is really just meant to format dates for SQL statements.
Russ,
ReplyDeleteThat 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.)
JD was right, try this:
ReplyDeletedateformat(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.
Timothy,
ReplyDeleteI 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...
A couple of thoughts here.
ReplyDeleteThe 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
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?
ReplyDelete--
Adam
Adam,
ReplyDeleteThe 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.