jeudi 14 avril 2016

Error message Conversion failed when converting datetime from character string

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 +')' 
        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.

Aucun commentaire:

Enregistrer un commentaire