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
<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 »
Ben Nadel
Nov 20, 2009 at 11:00 PM
Five Months Without Hungarian Notation And I'm Loving It
@Marcel, Yeah, I always err on the side of longer but more readable variable names. As for the camel casing of CF methods and the headless camel casing of custom items, I get around this by always ... read »
Marcel
Nov 20, 2009 at 10:56 PM
Five Months Without Hungarian Notation And I'm Loving It
I use the following and love it: my.namespace.MyComponents.functionMethodsOrUDF() CONSTANT_VALUES_OR_PROPERTIES One thing I always try is to CamelCaseBuiltInColdFusionFunctions() so others can tell ... read »
Darren
Nov 20, 2009 at 5:38 PM
Learning ColdFusion 8: CFImage Part I - Reading And Writing Images
Hi Ben, Great article. I've been looking around to see if ColdFusion image engine can programatically create the following "wrap around" effect: http://www.creativepro.com/article/photoshop-s-she ... read »
Todd Rafferty
Nov 20, 2009 at 5:35 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Dave: I talked to Gert he suggested: <cfhttp method="get" url="http://{some cf website}" result="stuff" addtoken="yes" /> Note the addition of cfhttp attribute addtoken. That should persist y ... read »
Ben Nadel
Nov 20, 2009 at 5:23 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
@Todd, Ahh, gotcha, yeah that makes sense. ... read »
Todd Rafferty
Nov 20, 2009 at 5:17 PM
Maintaining ColdFusion Sessions Across SMS Text Message Requests Without Cookies
Ben, sorry if I didn't make this clear. You can make it work like that if you want, just put <cfset session.foo = 1> (and <cfset application.foo = 1>) in your OnRequestStart() and it reve ... read »