CODEWITHSUNDEEP
sql-server-2008t-sql
tam tam
tam tam

Reputation: 1900

Passing datetime paramters in Dynamic SQL, Conversion failed when converting date and/or time from character string

I have a property in a table called cdate which is stored as such: 2014-10-07 05:26:17.897.

my dynamic query is something like this, CDATE is a datetime in the table and startdate and enddate are always passed and they are both datetime as well. My where clause is in a dynamic SQL query and it returns some records but for instance if CDate = 2014-10-07 05:26:17.897 and I am passing start date as 09/30/2010 08:15 pm and end date 10/07/2014 08:15 pm, i do not get my intended record back

SELECT  
CONVERT(VARCHAR(10),ServiceEntry.CDate,120) as CDate
FROM TABLE
WHERE
(Table.CDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')

my original query

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
     SELECT
       TSBNumber [TSBNumber],
       SystemFullName,
       CONVERT(VARCHAR(10),ServiceEntry.ClosedDate,120) as ClosedDate
     FROM ServiceEntry 
     inner JOIN System 
       ON ServiceEntry.SystemID = System.ID
     where
      (ServiceEntry.TSBNumber IS NOT NULL)
       AND 
       (ServiceEntry.ClosedDate IS NOT NULL)
       AND
       ( 
       (''' + @SelectedTsbIDs + ''' = '''+ '0' + ''') OR
         (ServiceEntry.TSBNumber in (select * from dbo.SplitStrings_Moden(''' + @SelectedTsbIDs + ''', ''' + ',' + ''')))
        )  
        AND (
         (''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = '''+ '0' + ''') 
        OR(System.PlatformID = ''' + cast(@PlatformID as varchar(10)) + ''')
        OR(''' + CAST(@PlatformID AS VARCHAR(10)) + ''' = ''' + '12' + ''' AND System.PlatformID <=  ''' + '2' + ''')
        )
        AND
   (ServiceEntry.ClosedDate between ''' + convert(varchar(10), @StartDate, 120)  + '''  and   ''' +  convert(varchar(10), @EndDate, 120) + ''')

Upvotes: 1

Views: 1392

Answers (1)

radar
radar

Reputation: 13425

Don't convert @StartDate, @EndDate to varchar as you need date comparison

SELECT  
    CONVERT(VARCHAR(10),ServiceEntry.CDate,120) as CDate
FROM 
    TABLE
WHERE
    Table.CDate BETWEEN @StartDate AND @EndDate

EDIT:

AS OP wants in dynamic query it should be written like this

@query = ' Declare @StartDate datetime = ' + value1  + ' Declare @EndDate datetime =' + value2 + ' SELECT  
    CONVERT(VARCHAR(10),ServiceEntry.CDate,120) as CDate
FROM 
    TABLE
WHERE
    Table.CDate BETWEEN @StartDate AND @EndDate '

exec(@query)

Upvotes: 1

Related Questions