SQL Server: fetching by date range returns error

Question

I have a dynamic stored procedure that includes the following input variables and Where condition with the column R.dateRec being formatted as datetime.

@rangeStart datetime,
@rangeEnd datetime

AND   R.dateRec BETWEEN ' + @rangeStart + ' AND ' + @rangeEnd + '

When I run the procedure without this condition than it works well but when adding this it returns the following error, maybe because it doesn't recognise the values as dates:

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

Can someone tell me how I can prevent this from happening ? The column in question only contains valid dates (no blank or NULL values).

Many thanks in advance, Tim.

krawl · Accepted Answer · 2014-06-25 13:49:01Z

2

need to convert the date parameters to varchar first if it is a dynamic sql...

added quotes

{beginning of query...} + ' and R.dateRec between ''' + convert(varchar, @rangeStart, 111) + ''' and ''' + convert(varchar, @rangeEnd, 111) + ''' {continue query}'

edited Jun 25, 2014 at 13:49

answered Jun 25, 2014 at 13:33

krawl

3991 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2571510 Over a year ago

Thanks. It's a dynamic procedure so I cant keep it like that.

user2571510 Over a year ago

Thanks for the update - this works perfect ! SQL sometimes can be picky. :)

Mark Wojciechowicz · Accepted Answer · 2014-06-25 13:49:36Z

0

are you missing quotes? i.e.

   AND   R.dateRec BETWEEN ''' + @rangeStart + ''' AND ''' + @rangeEnd + '''

answered Jun 25, 2014 at 13:49

Mark Wojciechowicz

4,4871 gold badge20 silver badges25 bronze badges

1 Comment

user2571510 Over a year ago

Thanks. That didn't work but the solution from krawl resolved it.

Collectives™ on Stack Overflow

SQL Server: fetching by date range returns error

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related