vendredi 15 avril 2016

How to convert/cast column data type in concatenated SQL query string

i have the following variables defined in my stored procedure

@StartDate  DateTime,
@EndDate    DateTime,

I'm setting the sql to be executed dynamically, so when constructing the query where clause i have the below line.

SET @sql = @sql + ' AND (convert(datetime, R.ReportDate, 121) >= ' + @StartDate + 'AND  convert(datetime, R.ReportDate, 121) <=' + @EndDate +')' 

When i execute the stored procedure, the line above throws the error below

Conversion failed when converting datetime from character string.

If i change the variable datatype to NVARCHAR(MAX), the procedure executes successfully but then returns no rows because the date comparison/matching fails.

ReportDate column is of datatype datetime and has data in this format 2014-06-01 00:00:00.000

As you can see i have tried converting the column when constructing my query but that isn't working.

Aucun commentaire:

Enregistrer un commentaire