CF_SQL_DATE Issues with MS SQL Server 2005
Posted April 16, 2006 at 12:00 AM
At work, we are using MS SQL Server 2000. Works great. At home though, I have more of a developer's environment. I am running CF Developer's edition and MS SQL Server 2005 single user edition (or something, can't quite remember what it is).
I don't get a lot of time to work on stuff at home, but one night, I had some time to kill, so I brought a personal site from work to home. I copied the files over, created the datasource, started her up and she opened file... until I started to click around. Once I went to any DB driven page I got a SQL error that "optional components had not been installed." Not being a SQL master, I had no idea what this meant, nor did I have any idea how to debug it. So, I just started taking out parts of the query until things started running again.
Apparently, what was causing the error was my use of the CF_SQL_DATE sql type in my query params. Not having internet at home (crazy I know), all I could do was look up the CF help files. Well, in the CF query param help files, apparently CF_SQL_DATE doesn't actually translate very well to MS SQL server, if I understood the documentation correctly. It seems that CF_SQL_TIMESTAMP is the one type that translates over to DATETIME in MS SQL server.
So I went back into the query, replaced the DATE with the TIMESTAMP, ran the page again and presto-chango, works like a charm. I then did this for all the pages on the site and everything is running great.
The only caveat with TIMESTAMP is that is uses the TIME part of the date, where as CF_SQL_DATE, I believe, did not. So now, I just have to be sure to use date's that only have date portions before using them in the database.
Comments (5) | Post Comment | Ask Ben | Permalink | Print Page
Reader Comments
For the love of god... thanks so much for posting this. I was having one of those "this is way too easy for it not to be working" moments up until I realized that the SQL Server version was the variable in my equation. Take care!
Posted by Michelle Kinsey Bruns on Nov 30, 2006 at 5:09 PM
Hi Ben,
I had a problem with cf_sql_date on MySQL for a simple query that should run on MSSQL2000 and 2005 as well. It seemed that I didn't get the timestamp into the db-field when using this cfsqltype, even when requiring the db-field to be filled with createodbcdatetime(). Taking the hint from your article I switched to cfsqltimestamp and voila, on all db-server environments I got the required timestamp ;-)
Merci!
Posted by Sebastiaan on Feb 2, 2008 at 5:45 AM
Thank you posting this and for your blog.
I finally found this blog entry using Google and switching the cfsqltype from cf_sql_date to cf_sql_timestamp solved the problem I was having querying a MS SQL Server 2000 table.
Posted by Bruce on Apr 8, 2008 at 2:10 PM
@Bruce,
Glad to help :)
Posted by Ben Nadel on Apr 8, 2008 at 5:44 PM
Love it, saved my ass ;)
Posted by Dominic Watson on Apr 21, 2008 at 8:21 PM
