Caution: ColdFusion Zero Date vs. SQL Zero Date

Posted September 22, 2006 at 8:21 AM

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:

 Launch code in new window » Download code as text file »

  • <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).

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page




Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

Reader Comments

There are no comments posted for this web log entry.


Post Comment  |  Ask Ben

Recent Blog Comments
Jody Fitzpatrick
Nov 21, 2009 at 5:15 PM
Using ColdFusion Structures To Remove Duplicate List Values
@Jose Galdamez, Oh heh yeah I didn't paste the whole code. I should have defined the vars -- my bad. It's fixed thou. Thanks. ... read »
Unibands
Nov 21, 2009 at 4:49 PM
Styling The ColdFusion 8 WriteToBrowser CFImage Output
Great work yet again Ben! Whilst I didn't use this whole code, I copied some of your regex code for a similar problem with the lack of an alt attribute and unescaped ampersands in CFIMAGE for Railo 3 ... read »
Justice
Nov 21, 2009 at 1:13 PM
My First ColdFusion Builder Extension - Encrypting And Decrypting CFM / CFC Files
@Ben, Because I am pedantic, I just want to make sure that everyone knows there is absolutely no encryption going on. There is only encoding and obfuscation. The cfencode tool only obfuscates your C ... read »
Jose Galdamez
Nov 21, 2009 at 12:28 PM
Using ColdFusion Structures To Remove Duplicate List Values
@Jody I can't seem to get your code sample to work. If you are still having problems, try this code out and see if it gets you what you wanted. <!--- Comma delimited list with various duplicates ... read »
Esther Annies
Nov 21, 2009 at 11:03 AM
Groovy Operator Overloading Does Not Work In The ColdFusion Context
Hi Ben, Thanks for this informative post. Now I am reading ur old posts too ... read »
Henry Parker
Nov 21, 2009 at 10:56 AM
HostMySite.com Has The Best ColdFusion Hosting
@Mehul, Yes very nice people, however several downtimes per day which was not acceptable. Hence we had to move out. I am glad you are having good luck with them so far. ... read »
<scriptalert('just a test buddy :)')script
Nov 21, 2009 at 12:41 AM
Ask Ben: Building An AJAX, jQuery, And ColdFusion Powered Application
13311qseqweqwe ... read »
Budd Wright
Nov 20, 2009 at 11:32 PM
Five Months Without Hungarian Notation And I'm Loving It
I've used headless camel case for years for not only ColdFusion variables, but also SQL tables and fields... pretty much everything involving code. I also subscribe to the "don't abbreviate and clea ... read »