run sprocs with date

  • 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?

  • 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 ....

  • Ya this is a bad idea to use strings instead of dates... 1 of the very reasons you just found about.

  • 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'

  • 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)

  • 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...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply