December 13, 2011 at 9:54 am
I am facing a very weird issue with one of my stored procedures. SP is just doing a basic select statement with a where clause on a date (smalldatetime). This SP runs every morning via a SQL Reporting Services report. The first time it runs in the morning, it errors out:
"cannot convert varchar value to smalldatetime"
I then go to Mgmt Studio and modify the SP by just adding a space somewhere and then recompile it by clicking the Execute button. Once recompiled the SP runs fine.
Not sure why I get the error and why the same query works after recompilation?
SELECT coalesce(Customer.AccountServiceAddress.SwitchRestrictionDate,Customer.AccountServiceAddress.EnrollmentRequestedDate) AS EnrollmentDate,
FROM Customer.AccountServiceAddress
WHERE ((CAST(CAST(Customer.AccountServiceAddress.SwitchRestrictionDate AS CHAR(11)) AS smalldatetime)) >= @StartDate OR
(CAST(CAST(Customer.AccountServiceAddress.EnrollmentRequestedDate AS CHAR(11)) AS smalldatetime)) >= @StartDate)
December 13, 2011 at 10:07 am
This is a much better way of doing that =>
DECLARE @StartDate DATETIME
SET @StartDate = DATEADD(D , 0 , DATEDIFF(D , 0 , GETDATE()))
SELECT
'Comment this out'
, @StartDate
, CAST(CAST(GETDATE() AS CHAR(11)) AS SMALLDATETIME)
SELECT
COALESCE(ADS.SwitchRestrictionDate , ADS.EnrollmentRequestedDate) AS EnrollmentDate
FROM
Customer.AccountServiceAddress ASD
WHERE
(
ADS.SwitchRestrictionDate >= @StartDate
OR ADS.EnrollmentRequestedDate >= @StartDate
)
December 14, 2011 at 8:00 am
Thanks.. It worked..
Still don't know why, but it worked 🙂
December 14, 2011 at 8:05 am
Convert to <var>char is a string manipulation. This is dependant on local settings such as langages, dateformat, datefirst, etc.
If you only do mathematical equation leaving the data in it's natural form you eliminate any conversion error. That also makes your app work worldwide.
Removing the convert from the column also gives the possibility to do an index seek. Since it seems to be a cut off version of the query I can't garantee it but it's a <low> possibility.
Finally by setting a local date variable it cleans up the query and makes it clearer as to the intent.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply