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

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

Answer 1

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

.Sander
@svandenhoven

Answer 2

 Use  DATEADD(yer,1, cast('20150315' as datetime)) 

Also check DateDiff if you just want to get the difference

Thanks.

Answer 3

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):
http://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx

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.

---

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