Skip to main content
W
wed2deb
Participant
September 19, 2008
Question

DateAdd DST bug in Cfquery?

  • September 19, 2008
  • 2 replies
  • 542 views
Hello! We ran into an interesting apparent bug today in cfquery regarding daylight savings. If you look below, this is the content of the cfdump when we tried to do simply "select dateAdd(hour,1,'3/11/2007 01:00:00');" from within a cfquery. It adds properly to the hour before or the hour after, but at that hour (which is the dst switch hour), it adds 2! Any ideas? Anyone run into this before?

CODE:
<cfquery name="qry" datasource="test">
select dateAdd(hour,1,'3/11/2007 01:00:00');
</cfquery>
<cfdump var="#qry#">

RESULTS:
query
RESULTSET query
COMPUTED_COLUMN_1
1 2007-03-11 03:00:00.0

CACHED false
EXECUTIONTIME 1
SQL select dateAdd(hour,1,'3/11/2007 01:00:00');
This topic has been closed for replies.

2 replies

N
Newsgroup_User
Inspiring
September 20, 2008
-==cfSearching==- wrote:
> You are calling a database function _not_ a CF function. So any issues are with whatever database you are using, not CF.

yes well there is that but cf will certainly roll over a datetime on the cusp of
DST before anything else happens (not 100% sure in this case w/a string but of
cf ever gets it's paws on a datetime, welcome to tz hell ;-).
-
-__cfSearching__-
Inspiring
September 21, 2008
PaulH wrote:
> yes well there is that but cf will certainly roll over a datetime on the cusp of
> DST before anything else happens (not 100% sure in this case w/a string but of
> cf ever gets it's paws on a datetime, welcome to tz hell ;-).

Just the person I was hoping would chime in on this one. I did some checking and of course you are right :-) MS SQL does an implicit convert to datetime. So it definitely applies here. In my tests the MS SQL time is 2:00AM. Once the datetime value hits CF the time changes to 3:00AM. Which is consistent with this TechNote description:
http://kb.adobe.com/selfservice/viewContent.do?externalId=d2ab4470

But I think my brain is still swirling around in the whirlwinds of time zone hell. On the one hand there is CF adjusting the datetime values. But on the other there is MS SQL returning March 11, 2007 2:00AM, which is technically 3:00AM in certain DST zones. So .. what is the right answer in this case ?
-
-__cfSearching__-
Inspiring
September 19, 2008
wed2deb wrote:
> select dateAdd(hour,1,'3/11/2007 01:00:00');

You are calling a database function _not_ a CF function. So any issues are with whatever database you are using, not CF.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices