Date issue with ColdFusion and SQL Server Stored Procedure
I had an unexpected issue today with a SQL Server stored procedure that worked fine in SQL Server Management Studio (SSMS), but threw the following error when I ran it in ColdFusion:
[Macromedia][SQLServer JDBC Driver][SQLServer]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
This was odd as the query in CF doesn’t actually contain reference to any dates. They are all handled internally inside the stored procedure.
Generally, when I’m working with dates, they’ll start as datetime objects in the ColdFusion space, and get passed to SQL Server via CFQUERYPARAMs, but in this case they were coming from a datetime column in another SQL database.
The procedure does a fair bit of stuff, building up an UPDATE statement into a variable as it goes, which then gets EXECed at the end. I was CONVERTing the dates using format 21, which is described in Books Online as “ODBC canonical (with milliseconds)” and seemed like a reasonably unambiguous way of representing the date as a string which I could concatenate with my growing SQL statement. I ended up with a SQL statement that looked something like:
UPDATE table SET date = '1980-05-23 00:00:00' WHERE id = 1
Knocking up a minimal test with just this statement, I got the same results – error from CF, fine when run in SSMS. Obviously either CF or the JDBC driver were treating the varchar representation of the date differently than SSMS.
My first guess, dropping the quotes around the date, didn’t help as that produced a syntax error in both environments, so I needed to find a way of getting the date into a format that CF didn’t have a problem with.
Since I’ve never had this problem with dates in ColdFusion before, I ended up looking at the format it uses itself when you run a statement like
UPDATE table SET date = #createdate(1980,05,23)# WHERE id = 1
That gets translated into
UPDATE table SET date = {ts '1980-05-23 00:00:00'} WHERE id = 1
using the ODBC timestamp format. Adjusting the stored proc to build the SQL string using this format for dates, everything now works as expected, but I’m still at a bit of a loss as to why CF cares (or even knows about) what is going on inside a stored procedure.