July 11, 2011 at 5:59 pm
I'm running a procedure other one wrote.
It has a input parameter EndDate VARCHAR(10)
and in the stored procedure it calculated another variable called @StartDate. It is basically one data after the end date for next year.
@StartDate = CAST(CAST(@EndDate AS INT)+1 AS VARCHAR(10));
My question is when I execute this sproc, I entered the input parameter EndDate like '07/15/2011'
but when I execute, I got an error:
Conversion failed when converting the varchar value '07/16/2011' to data type int.
Why is that?
July 11, 2011 at 7:18 pm
The parameter is a varchar (a string), and must be converted to an int for his calculation.
So it can't have / in it.
assuming he wants year last (is start date meant to be a year LATER than end date?), try '07152011'
The bigger question is why he has string date parameters in the first place, though ....
July 11, 2011 at 8:03 pm
Ya this is a bad idea to use strings instead of dates... 1 of the very reasons you just found about.
July 11, 2011 at 8:07 pm
I guess the reason he uses this is to easily calculate the startdate.
by +1.
I tested by passing the parameter @Enddate='20110715', it works. If I pass '07152011' it will not work, nor '07/15/2011'
July 11, 2011 at 8:08 pm
use the working format or change the parameter datatype. Nothing else you can do about this.
Best practice would also tell you to set and explicit DATEFORMAT (IE : SET DATEFORMAT YMD or whatever you want)
July 11, 2011 at 8:49 pm
Here is another method to add 1 year and 1 day as the OP requested
DECLARE @EndDate VARCHAR(10)
DECLARE @StartDate VARCHAR(12)
SET @EndDate = '07/15/2011'
SET @StartDate = DATEADD(DD,1,DATEADD(YY,1,(CAST(@EndDate AS DATETIME))))
SELECT CONVERT(DATETIME,@StartDate,101) as 'DATETIME VALUE',@EndDate AS 'Input date'
Results:
DATETIME VALUE Input date
2012-07-16 00:00:00.000 07/15/2011
And as Ninja's_RGR'us said earlier,
Ya this is a bad idea to use strings instead of dates...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply