Comparing string with SmallDateTime used to work
I'm trying to debug an old VB6 program at my job. It's full of bugs and hardcoded stuff.
The program is connecting on a SQL Server 2005 database to read and write info using stored procedures.
Since the program is still being used while I'm working on it, I made a backup of the database to test some things and restored it in my version of SQL Server 2008. Everything was working fine.
When I came in the next morning, I got an error I didn't have the previous night even though the program wasn't used in the night.
The problem :
"exec dbo.sp_Get_Wait_Data '" & DateEx & "'"
DateEx is a string containing "2012/06/14"
The stored procedure :
[sp_Get_Wait_Data] @Datest as char(10) AS SELECT (A lot of column names here) FROM Fiche LEFT JOIN voyage ON fcid = vofiche LEFT JOIN client on fcaccount = cusnov WHERE fcdate = @Datest AND (void is null or (void > 0 and (void <> 999 and void <> 1000 and void <> 998))) AND ((fcremarques NOT LIKE '%SYSANNULATION%' OR fcremarques IS NULL) AND fcrettime IS NOT NULL) ORDER BY FcTime, FcOrgSite, fcdessite
The error message :
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value
So the error is here fcdate=@Datest in the stored procedure. I tried adding this line in the stored procedure
SELECT convert(datetime, @Datest, 120)
which worked like a charm for the convertion within the query but caused a few hundred other errors within the program when other queries tried to access this variable so it is not an option.
Any idea why this query was working perfectly yesterday and now it's giving me this error? Thanks in advance.
You could change the format of the parameter to YYYYMMDD which will be safe regardless of set dateformat or even better, change your parameter to datetime.
If that is not an option you can rewrite your query using where fcdate=convert(datetime, @Datest, 111)