Conversion failed when converting date and/or time from character string. RRS feed

  • Question

  • Hi Experts,

    I Have dynamic storedprocedure
    @FromDate varchar(10),
    @ToDate varchar(10)

    UpDate format 20130430

    GH.UpDate  Between '''+@FromDate+''' and '''+@ToDate+' Then its working fine.

    When iam trying to use Dateadd and convert it giving error

    GH.UpDate  Between '''+dateadd(yer,-1,@FromDate)+''' and '''+convert(datetime,@ToDate,103)+'

    Conversion failed when converting date and/or time from character string.

    I tryied using cast and  dateadd same error is comming
    +DATEADD(yyyy,-1,@@FromDate)+''' and '''+@ToDate+'

    EXEC SP '20150315' ,'20150922'

    Please help me

    Thursday, June 9, 2016 6:06 AM


All replies

  • Hi,

    You can use select DATEADD(yyyy,1, cast('20160609' as datetime)) for date conversion and adding time to a data.

    If this reply answers your question please mark it as answer, so we can help others


    Thursday, June 9, 2016 6:33 AM
  •  Use  DATEADD(yer,1, cast('20150315' as datetime)) 

    Also check DateDiff if you just want to get the difference


    Thursday, June 9, 2016 6:38 AM
  • Hi,

    Your basic mistake is that you uses string as input, instead datetime type!

    your SP should get DateTime/Date/DateTime2 as input, for example.

    Converting from string to date is non-deterministic action, which can and usually do leads to mistakes. Please check this blog for more information and the right solution (if you can't use DateTime as input and you must use string):

    Implicit conversion of ambiguous date formats are interpreted according to the language of the connection. Always keep and follow the rules in the blog above.

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, June 9, 2016 6:38 AM