Error message Conversion failed when converting datetime from character string

Question

    ALTER  PROCEDURE [dbo].[TEST_01]       
     (
        @StartDate  DateTime,
        @EndDate    DateTime
     )
    AS      
    BEGIN      
      SET NOCOUNT ON;  
      Declare @sql as nvarchar(MAX);
      SET @sql = @sql + ';WITH CTE_ItemDetails
                MAX(D.Name) as Name,
                SUM(ISNULL(DT.col1, 0)) AS col1,
                SUM(ISNULL(DT.col2, 0)) AS col2,
                SUM(ISNULL(DT.col3, 0)) AS col3,
                GROUPING(D.ItemType) AS ItemTypeGrouping
            FROM Items D
                INNER JOIN Details DT ON DT.ItemId = D.ItemId
                INNER JOIN Report R ON R.ReportId = DT.ReportId
                where 1=1'
            SET @sql = @sql + ' AND (R.ReportDate >= ' + @StartDate + 'AND  R.ReportDate <=' + @EndDate +')' 

            IF @someOtherVariable is not  null
            SET @sql = @sql + ' AND R.someColumn IN (''' +replace(@someOtherVariableValues,',','')+''+')' 

            SET @sql = @sql + 'SELECT col1, col2, col3 FROM CTE_ItemDetails'
            EXECUTE (@sql)  
    END

I have a stored procedure that is similar to the T-SQL code above. (Note that i have removed lots of code that i feel isn't relevant to the error i'm getting) I'm getting the below error when i execute it.

Conversion failed when converting datetime from character string.

My parameters have values in below format

exec TEST_01 @StartDate=N'4/1/2016 12:00:00 AM',@EndDate=N'4/30/2016 12:00:00 AM'

It looks like the trouble is in the way i'm dynamically setting the SQL statement at line below

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

What is the best date formatting i can apply to avoid the error.

You need to quote dates, you should change it to @sql + ' AND (R.ReportDate >= ''' + @StartDate + ''' AND R.ReportDate <= ''' + @EndDate + ''')' — Siyual
– Siyual, Commented Apr 14, 2016 at 14:09

Gordon Linoff · Accepted Answer · 2016-04-14 14:08:07Z

1

You should use parameters via sp_executesql.

But your immediate problem is this line:

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

It should look more like:

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

Note the inclusion of explicit type casting to a string and the double single quotes so the date literal is not interpreted as 2016 - 04 - 14 (i.e. 2000).

The better method of using parameters looks like:

    SET @sql = @sql + ' AND (R.ReportDate >= @StartDate AND R.ReportDate <= @EndDate)' ;

. . .
exec sp_executesql @sql, N'@StartDate date, @EndDate date)', @StartDate = @StartDate, @EndDate = @EndDate;

It is easier to read the SQL statement. The type issues are handled through parameters. And, the query plan is more readily stashed. Unfortunately, parameters only work for constants, not for column or table names, for instance.

answered Apr 14, 2016 at 14:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

StackTrace Over a year ago

what will happen if i use sp_executesql and pass a parameter that has not been added to @sql string because it was found to be null or empty?. I'm asking this because i'm adding a few more parameters (not shown in the script i posted initially). I have updated my the code to show this.

Gordon Linoff Over a year ago

@StackTrace . . . If the parameters are declared in the second and third arguments, then it doesn't matter if it is in the SQL string.

Collectives™ on Stack Overflow

Error message Conversion failed when converting datetime from character string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related