Date issue in dynamic SQL

  • I am having an issue with dynamic sql.

    I am getting this error:

    Msg 402, Level 16, State 1, Line 27

    The data types varchar and date are incompatible in the add operator.

    If I run the query normally, it works fine. But if I try to put it in a variable but splitting out the variable, I get the error.

    This part works fine.

    DECLARE @DateRange INT

    DECLARE @sql VARCHAR(2000),

    @EndDate Date

    SELECT @DateRange = 12

    SELECT @EndDate = DATEADD(MONTH,-@DateRange,GETDATE())

    CREATE TABLE #Temp

    (

    DocDate DATETIME

    )

    INSERT #Temp (DocDate) VALUES (GETDATE())

    SELECT *

    FROM #Temp

    WHERE (CAST(DocDate AS DATE) BETWEEN CAST(@EndDate AS DATE) AND CAST(GetDate()AS DATE))

    But if I just put the SELECT statement into the following string, it gets the error:

    SELECT @sql = 'SELECT *

    FROM #Temp

    WHERE (CAST(DocDate) BETWEEN CAST(' + @EndDate + ' AS DATE) AND CAST(GetDate()AS DATE))'

    I also tried to move the quotes around the CAST statement and got the same error:

    SELECT @sql = 'SELECT *

    FROM #Temp

    WHERE (CAST(DocDate) BETWEEN ' + CAST(@EndDate AS DATE) + ' AND CAST(GetDate()AS DATE))'

    How do I change the statement to fix this?

    Thanks,

    Tom

  • CAST(@EndDate AS VARCHAR(10))

  • I don't understand.

    Why does it work without casting it to varchar in the first version?

    Thanks,

    Tom

  • tshad (5/14/2015)


    I don't understand.

    Why does it work without casting it to varchar in the first version?

    Thanks,

    Tom

    You are missing the data type in the first cast in the where clause!

    😎

    My question is why on earth would you ever want to do it this way? From what I can see here, there are no benefits from the dynamic sql, no index can satisfy the query, it is wide open to injection, the adhoc plan will not be cached etc. etc.

  • The reason it doesn't work in either of your original versions is because they are both attempting to concatenate a date datatype to a varchar data type... The two data types are incompatible. Casting the date to varchar allows the concatenation to work.

  • The reason I am doing it this way is that this select is really part of an OpenQuery statement. And if you use parameters, apparently you have to put the whole statement into a string and Exec it.

  • That makes sense.

    Would it be better to surround the @Enddate in the first example with a Convert to Varchar or the second one (the cast statement)?

    Thanks,

    Tom

  • It depends... CAST is typically the preferred syntax being that it's an ansi standard... But, sometimes it's necessary, when working with different date/time data types to use the CONVERT function in order to use the optional style parameter.

    Basic rule of thumb... If CAST will work, use CAST... When it won't, use CONVERT and set the appropriate style value.

  • Never use a function on a column if it can be avoided.

    Never use between for date/datetimes, use >= and < instead.

    SELECT @sql = 'SELECT *

    FROM #Temp

    WHERE DocDate >= ''' + CONVERT(char(8), @EndDate, 112) + ''' AND ' +

    'DocDate < ''' + CONVERT(char(8), DATEADD(DAY, 1, GETDATE()), 112) + ''''

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Or you could prevent all that casting by parametrizing your query.

    SELECT @sql = 'SELECT *

    FROM #Temp

    WHERE DocDate BETWEEN @EndDate AND GetDate()'

    EXEC sp_executesql @sql, N'@EndDate date', @EndDate

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (5/15/2015)


    Or you could prevent all that casting by parametrizing your query.

    SELECT @sql = 'SELECT *

    FROM #Temp

    WHERE DocDate BETWEEN @EndDate AND GetDate()'

    EXEC sp_executesql @sql, N'@EndDate date', @EndDate

    True, if you want to execute the query directly, but then there is no reason to use dynamic sql for that query. Now, if you are going to use this in an OPENQUERY (which is what the OP is doing), you can't as variables aren't allowed in the query.

  • If an OPENQUERY statement could make use of the @EndDate parameter, he wouldn't need dynamic SQL in the first place.

    I have a lot of code that references our non-SQL accounting system, and the drivers require OPENQUERY statements instead of four-part names. So I run into this issue all the time. Some of the code I inherited builds dynamic SQL with so many concatenated expressions that it is hard to see what the statement is doing.

    I prefer to use a template statement with replacement tags for all the parameters, then surround it with a REPLACE() function for each tag.

    SET @cmd = REPLACE(REPLACE(REPLACE(

    'SELECT * FROM OPENQUERY(Link, ''SELECT * FROM tbl

    WHERE intcol=<intval> AND strcol="<strval>" AND dtcol > <dateval>'')',

    '<intval>', @Int),

    '<strval>', @STR),

    '<dateval>', CONVERT(CHAR(10), @Date, 101));

    This would be formatted for the system I deal with, which uses double-quote delimiters for string literals and accepts date literals with no delimiters but only in the format MM/DD/YYYY.

    I find this to be a lot more readable, especially if some tags are used more than once in the template. It's much easier to verify that the basic statement has the right number of delimiters in all the right places. The replacement values can be expressions, and the REPLACE function will do an implicit CAST to VARCHAR of things like integers. I'm now dealing with another level of linked servers so I have nested OPENQUERYs, with plenty of doubled and quadrupled quotes. If I had to add parameters using "...''''' + CAST(@Int AS VARCHAR) + '''''..." I would go nuts counting the stupid quotes. And fighting with Intellisense when it decides to add extras.

  • Scott Coleman (5/15/2015)


    If an OPENQUERY statement could make use of the @EndDate parameter, he wouldn't need dynamic SQL in the first place.

    I have a lot of code that references our non-SQL accounting system, and the drivers require OPENQUERY statements instead of four-part names. So I run into this issue all the time. Some of the code I inherited builds dynamic SQL with so many concatenated expressions that it is hard to see what the statement is doing.

    I prefer to use a template statement with replacement tags for all the parameters, then surround it with a REPLACE() function for each tag.

    SET @cmd = REPLACE(REPLACE(REPLACE(

    'SELECT * FROM OPENQUERY(Link, ''SELECT * FROM tbl

    WHERE intcol=<intval> AND strcol="<strval>" AND dtcol > <dateval>'')',

    '<intval>', @Int),

    '<strval>', @STR),

    '<dateval>', CONVERT(CHAR(10), @Date, 101));

    This would be formatted for the system I deal with, which uses double-quote delimiters for string literals and accepts date literals with no delimiters but only in the format MM/DD/YYYY.

    I find this to be a lot more readable, especially if some tags are used more than once in the template. It's much easier to verify that the basic statement has the right number of delimiters in all the right places. The replacement values can be expressions, and the REPLACE function will do an implicit CAST to VARCHAR of things like integers. I'm now dealing with another level of linked servers so I have nested OPENQUERYs, with plenty of doubled and quadrupled quotes. If I had to add parameters using "...''''' + CAST(@Int AS VARCHAR) + '''''..." I would go nuts counting the stupid quotes. And fighting with Intellisense when it decides to add extras.

    +1

    😎

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply