CODEWITHSUNDEEP
sqlsql-serverdatetimestored-proceduresdynamic
user2571510
user2571510

Reputation: 11377

SQL Server: fetching by date range returns error

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.

Upvotes: 1

Views: 36

Answers (2)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

are you missing quotes? i.e.

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

Upvotes: 0

krawl
krawl

Reputation: 399

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

Upvotes: 2

Related Questions