Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with:

Caution: ColdFusion Zero Date vs. SQL Zero Date

By Ben Nadel on
Tags: ColdFusion, SQL

In the past, I have talked a lot about the FLOAT equivalents of date/time stamps in both ColdFusion and SQL. I have talked about how fast they are and how they can be manipulated. But, one thing that I have never really touched upon is how the zero dates in ColdFusion are different from the zero dates in SQL (well, at least how it operates on my server configuration).

Caution! They are different. Not only are they different, but you have to be careful in what format you pass dates to SQL. Take a look at the SQL statement below. I am comparing the SQL zero date to the ColdFusion zero date as well as testing how different DateFormat() calls are converted via the CFQueryParam tag and SQL:

  • <cfquery name="qZeroDate" datasource="...">
  •  
  • <!--- Select the SQL zero date. --->
  • SELECT
  • ( 'SQL Zero Date' ) AS label,
  • ( CAST( 0.0 AS DATETIME ) ) AS the_date,
  • ( 0.0 ) AS the_float
  •  
  • UNION
  •  
  • <!---
  • Select the DateFormat() ColdFusion zero date with
  • default date-mask.
  • --->
  • SELECT
  • ( 'CF Zero DateFormat( )' ) AS label,
  • (
  • <cfqueryparam
  • value="#DateFormat( 0 )#"
  • cfsqltype="CF_SQL_TIMESTAMP"
  • />
  • ) AS the_date,
  • CAST(
  • <cfqueryparam
  • value="#DateFormat( 0 )#"
  • cfsqltype="CF_SQL_TIMESTAMP"
  • />
  • AS FLOAT
  • ) AS the_float
  •  
  • UNION
  •  
  • <!---
  • Select the TimeStamp ColdFusion zero date (ie. Let
  • CFQueryParam make the conversion for us.
  • --->
  • SELECT
  • ( 'CF Zero TimeStamp' ) AS label,
  • (
  • <cfqueryparam
  • value="0"
  • cfsqltype="CF_SQL_TIMESTAMP"
  • />
  • ) AS the_date,
  • CAST(
  • <cfqueryparam
  • value="0"
  • cfsqltype="CF_SQL_TIMESTAMP"
  • />
  • AS FLOAT
  • ) AS the_float
  •  
  • UNION
  •  
  • <!--- Select the ColdFusion zero date with full date maks. --->
  • SELECT
  • ( 'CF Zero DateFormat( full )' ) AS label,
  • (
  • <cfqueryparam
  • value="#DateFormat( 0, 'full' )#"
  • cfsqltype="CF_SQL_TIMESTAMP"
  • />
  • ) AS the_date,
  • CAST(
  • <cfqueryparam
  • value="#DateFormat( 0, 'full' )#"
  • cfsqltype="CF_SQL_TIMESTAMP"
  • />
  • AS FLOAT
  • ) AS the_float
  • </cfquery>

When I dump out that query, here is what I get:


 
 
 

 
CFDump : Zero Date Comparison Test - ColdFusion vs. SQL  
 
 
 

As you can see, the zero date in ColdFusion is two days different from the zero date in MS SQL Server. Now, let me state clearly that I am not sure why this is or if it is just this way on my server. I can only state for a fact that it is this way on MY server. Notice also that the default DateFormat() mask in ColdFusion makes SQL think it is dealing with 1999. That is because the default DateFormat() mask uses a 2-digit year: "d-mmm-yy". When SQL sees the 2-digit year, it assumes we are talking 100 years later than we actually mean (Y2k much?).

So anyway, that is just a word of caution. Date "math" is awesome, I highly recommend it. Just remember that the date math in one universe (CF) doesn't necessarily convert directly to the date math in another universe (SQL).




Reader Comments

It's worse than that....
in SQL
Select @curDate = getDate()
Select
DATEADD(qq, DATEDIFF(qq,0,@curDate), 0) QuarterFirstDate,
DATEADD(qq, DATEDIFF(qq,-1,@curDate), -1) QuarterLastDate,
DATEADD(qq, DATEDIFF(qq,-1,@curDate), 0) NextQuarterFirstDate

easy enough right?
Well apparently SQL knows that 12/31/1899 is 1 quarter further away than 1/1/1900,
Select DATEDIFF(qq,0,@curDate),DATEDIFF(qq,-1,@curDate)

Gives us 443, and 444 quarters difference...

Coldfusion does not...
(lets start at 2, the difference between SQL 0 and CF 0)
#DATEDIFF("q",2,now())#
#DATEDIFF("q", 1, now())#
#DATEDIFF("q", 0, now())#
#DATEDIFF("q", -1, now())#
#DATEDIFF("q", -2, now())#
#DATEDIFF("q", -3, now())#
#DATEDIFF("q", -28, now())#

they all return 443 until we go back 28 days!,
looks like coldfusion is saying "literally" how many quarters of days can fit between the dates... not the actual difference in quarters.

Reply to this Comment

@Steve,

Luckily, I don't have to do too much "date diff" kinds of things in my programming. I find that kind of stuff to be frustrating, unless you are getting the difference in really concrete things like day, week, month. Doing things like weekday and weekendday start to get a little iffy in their support. Heck, I don't even really know what a quarter is when it comes to days.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.