CF_SQL_DATE versus CreateODBCDate
I was highly tempted to file this entry under a "Duh!" category ;) Why? Because I discovered today that I have been laboring under a misconception about cfqueryparam and the cf_sql_date type. While I have always liked cfqueryparam, and its many features, I could never understand why it did not handle date/time values like the CreateODBCDate function. Surprise! Today I discovered that it actually does.
When using CreateODBCDate in a query, the debug output clearly shows that it truncates the time portion and sends a date only to the database. This behavior comes in handy when querying columns that contain both date and time. You can easily to retrieve all records for a specific date range, because you do not have to worry about the time portion.
<cfset dateTimeNow = createDateTime(2008, 2, 14, 9, 0, 0)>
<--- original query --->
<cfquery name="getRecords" datasource="#dsn#">
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= #CreateODBCDate(dateTimeNow)#
</cfquery>
<--- query debug output --->
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= {d '2008-02-14'}
However, when using cfqueryparam it appears to send both the date and time value, but it does not.
<--- original query --->
<cfset dateTimeNow = createDateTime(2008, 2, 14, 9, 0, 0)>
<cfquery name="getRecords" datasource="#dsn#">
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= <cfqueryparam value="#dateTimeNow#" cfsqltype="cf_sql_date">
</cfquery>
<--- query debug output --->
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= ?
Query Parameter Value(s) -
Parameter #1(cf_sql_date) = {ts '2008-02-14 09:00:00'}
Using the MS SQL Profiler, against a SQL 2000 database, I was able to see that the actual value passed to the database is a date only.
exec sp_execute 2, 'Feb 14 2008 12:00AM'
So the parameter in the debug output is the value passed into the query. It is not the final value that is used by the database. If you think about it, it makes sense. But it did leave me with the mistaken impression that cfqueryparam was not handling the date values correctly.
Now I do not know if you can use cf_sql_date with date/time columns against an MS SQL 2005 database. But you can use it with MySQL as you can see from the results below. Added to that the Connector/J documentation says a date/time data type can always be converted to either a java.sql.Date or java.sql.Timestamp. So I feel comfortable using cf_sql_date with MySQL date/time columns.
I guess this all goes to show you do not always know something as well as you think you do ;)
Posts
7 comments:
In your second example should the datatype be "cf_sql_date" instead of "cf_sql_timestamp"?
Yes. Copy + paste error. Thanks for letting me know. I will update the code snippet.
You might also try CreateODBCDateTime() it correctly passes the date and time on every database that I have used.
Yes, you are right. But my goal here was a little different. I was looking for a simple way to pass dates only. So I could do comparisons like retrieve all records modified anytime today:
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= {d '2008-02-15'}
AND ModifiedDate < {d '2008-02-16'}
It is easy using the CreateODBCDate function because it truncates any time portion of date/time values. However, I was unaware that using cfqueryparam with cf_sql_date did the same thing. Making these two statements roughly equivalent
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= #CreateODBCDate(now())#
AND ModifiedDate < #CreateODBCDate(DateAdd("d", 1, now()))#
SELECT TestID, ModifiedDate
FROM Test
WHERE ModifiedDate >= <cfqueryparam value="#now()#" cfsqltype="cf_sql_date">
AND ModifiedDate < <cfqueryparam value="#DateAdd("d", 1, now())#" cfsqltype="cf_sql_date">
I met the same problem, just i wanted it to insert and the time part, and after research in internet i found out that the cfsqltype have to be cf_sql_timestamp.
@didci,
I think your goal may be different than what is described here. But yes, the proper cfsqltype to use can vary by database.
Just keep in mind that unlike cf_sql_date, the type cf_sql_timestamp will submit both date and time. So if you are trying to submit the date portion only, you will have to do some manipulation before-hand.
-Leigh
yes, i know, and i understand ur post, that's why i have sent mine :) so to say how it must be in the other case. which i was searching for in internet
Post a Comment