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 Scotch On The Rock (SOTR) 2010 (Brussels) with: Cyril Hanquez

CF_SQL_DATE Issues with MS SQL Server 2005

By Ben Nadel on
Tags: ColdFusion

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.

Tweet This Interesting post by @BenNadel - CF_SQL_DATE Issues with MS SQL Server 2005 Thanks my man — you rock the party that rocks the body!

Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

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!

Reply to this Comment

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!

Reply to this Comment

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.

Reply to this Comment

I am using the POI utility to upload data into a excel spread sheet. I had seen your code helps. The number part works but the date part is a problem in my case.

I tried to use the sqldatetime in my querynew declaration for the columns.

I tried to put the datatime as string in the row because javacast does not accept any other format. I tried float and double. int was out of option in anycase. But non work.

Can you help me?

But the javacast throws an exception.

--------------------------
An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.

The error occurred in D:\WebSites\WebServices\Aruna\Student_NoShow1\admin\test1.cfm: line 28

26 : <cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
27 : <!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
28 : <cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
29 : <cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
30 : </cfloop>

----------------------------

Reply to this Comment

sorry I used CF_SQL_TIMESTAMP in the querynew statement.

My code:

<!--- Create query to ouptut to excel. --->
<cfset qMovie = QueryNew(
"StudentFName,StudentLName,StudentID,InstructorName,CoursePrefix,CourseNo,Section,Semester,Date_1,Reason",
"CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,
CF_SQL_VARCHAR, CF_SQL_VARCHAR,CF_SQL_TIMESTAMP,CF_SQL_VARCHAR") />

<!--- Add rows to query. --->
<cfset QueryAddRow( qMovie, #GetAllSemester.recordcount# ) />

<!--- Set row data. --->
<cfloop query="GetAllSemester">
<Cfset rownum=#GetAllSemester.currentrow#>
rownum: #rownum#, #GetAllSemester.currentrow# ,
qMovie[ "StudentFName" ][ #rownum# ] = JavaCast( "string", "#Stud_FN#" )<br />

<cfset qMovie[ "StudentFName" ][ #rownum# ] = JavaCast( "string", "#Stud_FN#" ) />
<cfset qMovie[ "StudentLName" ][ #rownum# ] = JavaCast( "string", "#Stud_LN#" ) />
<cfset qMovie[ "StudentID" ][#rownum# ] = JavaCast( "string", "#STud_no#" ) />
<cfset qMovie[ "InstructorName" ][ #rownum# ] = JavaCast( "string", "#Fac_name#" ) />
<cfset qMovie[ "CoursePrefix" ][ #rownum# ] = JavaCast( "string", "#Area#" ) />
<cfset qMovie[ "CourseNo" ][#rownum# ] = JavaCast( "string", "#NBR#" ) />
<cfset qMovie[ "Section" ][ #rownum# ] = JavaCast( "string", "#Sec#" ) />
<cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
<!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
<cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
<cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
</cfloop>

Error is :

An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.

The error occurred in D:\WebSites\WebServices\Aruna\Student_NoShow1\admin\test1.cfm: line 28

26 : <cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
27 : <!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
28 : <cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
29 : <cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
30 : </cfloop>

Reply to this Comment

@Aruna,

Usually that error about modifying queries comes from trying to reference a query column that doesn't exist. You shouldn't get an error for storing data in a query, even if it's the wrong data type (the query doesn't really care until you try to actually use the data).

Usually, this is due to a spelling mistake or leaving a space somewhere.

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.