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

Question

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

I believe you need to convert table.cdate in the where clause the same as you have in the select. and is @startdate ALWAYS before @ENDDate? if not that's a problem and if @start or @end are NULL then you have a problem there as well... are you getting an error or just no results? — xQbert
– xQbert, Commented Oct 7, 2014 at 19:57
STartDate and EndDate will always be provided, the dates passed will have a timestamp in them. I get no results in the above use-case — tam tam
– tam tam, Commented Oct 7, 2014 at 20:00
wow I can't believe I missed this the first time... your converting a @startDate, @EndDate to varchar in the where clause... why? varchar datetime will seldom equal a true timestamp... which is why it's hit and miss... What are variables @startDate, @endDate defined as? and why convert them if they are not going to be rendered... Basically you're saying.... Take this REAL apple turn it into a picture of an orange now have the system compare the Real apple passed in to the orange you created... — xQbert
– xQbert, Commented Oct 7, 2014 at 20:04
@tam tam , don't cast startdate and enddate in the where caluse — radar
– radar, Commented Oct 7, 2014 at 20:05

radar · Accepted Answer · 2014-10-07 20:29:05Z

1

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)

edited Oct 7, 2014 at 20:29

answered Oct 7, 2014 at 20:06

radar

13.4k2 gold badges27 silver badges33 bronze badges

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

7 Comments

tam tam Over a year ago

where clause if you notice the syntax from my query it is in a dynamic query and if i dont cast it in the where clause then i get this error Conversion failed when converting date and/or time from character string.

radar Over a year ago

@tamtam, if it is dynamic query. it should be like this.

tam tam Over a year ago

i have already decalred the startdate and endate in my store procedure, please look at my post, i edited and added the original query

radar Over a year ago

@tamtam if you already declared, you can you use varchar(30) instead of 10 to fit time stamp too.

tam tam Over a year ago

I changed it to (ServiceEntry.ClosedDate between ''' + convert(varchar(30), StartDate, 120) + ''' and ''' + convert(varchar(30), EndDate, 120) + ''') and still the error Conversion failed when converting date and/or time from character string

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related