Skip to main content
W
web-eng
Known Participant
March 5, 2014
Answered

Why is there an offset when using DateConvert with DateCompare?

  • March 5, 2014
  • 2 replies
  • 2126 views

I'm developing on my local server and my current timezone is GMT +11 hours.

<cfset date1 = DateConvert("Local2UTC", now())>

<cfset date2 = '2014-03-06'> <!--- (Tomorrow at time of writing) --->

So, I was expecting that when I ran this code:

#DateCompare(date1,date2,'d' )#

that it would return a "-1" but it returned a "0" implying that the "day" was the same.

So, to double check I output:

DateConvert("Local2UTC", now())

grabbed its displayed value : {ts '2014-03-05 07:08:58'} and inserted that for comparison thus:
DateCompare("{ts '2014-03-05 07:08:58'}",date2,'d' ) and sure enough, it did return a "-1" reflecting that the day of date1 is indeed earlier than date 2.

Further investigation showed that  I had to add my timezone difference to date2 in hours thus:

<cfset date2 = '2014-03-06 11:00:00> to get

#DateCompare(date1,date2,'d' )#

to return a "-1".

Is this a bug or am I misinterpreting??

This topic has been closed for replies.
Correct answer BKBK


The code in your question is not definitive enough for a straight answer. To expose the bug, you need to select a timeDate value for date2 which is in between your Now() and dateConvert('local2Utc', now()) (1 hour in your case). So just copy the code below and make sure to select an appropriate value for date2 taking into account when you're running the test. Note that I'm outputting the values for date1 and date2 to the screen so you can see the "absolute values" they resolved to prior to dateCompare(). What I find on both my remote and local servers (CF10) is that instead of comparing with the date1 (UTC time as displayed on the screen) it compares with the now() time.

<cfset date1 = dateConvert('local2Utc', now())>

<cfset date2 = createDateTime(2014,08,09,13,20,00)>

<cfoutput>

<p>Time of test = #Now()#</p>

<p>date1 = #date1#</p>
<p>date2 = #date2#</p>

<p>dateCompare(date1, date2 ) = #dateCompare(date1, date2 )#</p>

</cfoutput>o

This is what was output to the screen

Time of test = {ts '2014-08-09 14:18:42'}

date1 = {ts '2014-08-09 04:18:42'}

date2 = {ts '2014-08-09 13:20:00'}

dateCompare(date1, date2 ) = 1


Thanks for your explanation. My bad: it is now clear I misunderstood. Sorry.

Apparently, in dateCompare, Coldfusion implicitly converts date2 to local UTC. I agree with you that this kind of interpretation is inadmissible. The developer should be the one to do the interpretation, and Coldfusion should simply compare the 2 values passed to the function. You should indeed report a bug.

2 replies

itisdesign
itisdesign
Inspiring
November 23, 2014

Hi web-eng,

I've added a CF9 vs CF10 code comparison to your ticket #3802931 which illustrates the CF9 bug that CF10 fixed.

Thanks!,

-Aaron

W
web-engAuthor
Known Participant
November 24, 2014

Aaron, thanks for your contribution, but the bug is not with dateConvert, it's with dateCompare.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
March 5, 2014

You are misinterpreting, a bit. Well, ColdFusion is, too.

Start with ColdFusion. Run the following test code:

<cfoutput>

isDate('0.1'): #isDate('0.1')#<br>

isDate('2a'): #isDate('2a')#<br>

isDate('3p'): #isDate('3p')#

</cfoutput>

ColdFusion will tell you that it recognizes '0.1', '2a' and '3p' as valid dates! What dates do they stand for?How does each translate to your time zone and locale? ColdFusion might be bending it like Beckham, who knows? We wouldn't go into that.

However, the moral is clear. If you wish to avoid ambiguity, especially when comparing datetimes, then avoid representing dates as strings. Convert all your datetimes to datetime objects. That will enable you to compare like with like. For example,

createdate(2014,3,6) in place of '2014-03-06';

dateConvert("Local2UTC", now()) in place of {ts '2014-03-05 07:08:58'};

createdate(2014,3,6,11,0,0) in place of '2014-03-06 11:00:00'.

W
web-engAuthor
Known Participant
March 5, 2014

Hmmm, I think I might have to revisit a whole bunch of code with dates, Doh!

I think your advice of comparing like with like is appropriate however I'm still getting an unexpected result.
Sooo:
DateConvert("Local2UTC", now())  returns {ts '2014-03-05 15:11:14'}
note: 5th day of month
DateCompare(createdate(2014,3,6),DateConvert("Local2UTC", now()),'d' )
returns a "0", implying that both are the same day of the month??
I've tried changing the value in the create date expression to:

createdate(2014,3,5) and createdate(2014,3,7).
The values returned from the compare statement are then -1  and 1 respectively.
In all cases the DateCompare statement behaves as though DateConvert("Local2UTC", now())  returns a date from the 6th day of the month instead of the 5th??

Now using like with like, but still not getting expected result??
Further thoughts??

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
March 5, 2014

First off, a correction. I wrote in my last post,

createdate(2014,3,6,11,0,0) in place of '2014-03-06 11:00:00'.

That should have been: createdatetime(2014,3,6,11,0,0) in place of '2014-03-06 11:00:00'.

Now, on to dateCompare. When I run the following code

<cfoutput>

dateConvert: #dateConvert("Local2UTC", now())#<br>

dateCompare: #dateCompare(createdate(2014,3,6),DateConvert("Local2UTC", now()),'d')#

</cfoutput>

I get

dateConvert: {ts '2014-03-05 20:15:33'}

dateCompare: 1

My time zone is UTC+01:00. I wonder what yours is.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices