Dynamic SQL and DATEADD with User Defined Variable in Number/2nd position

  • This syntax

    SET @sql = 'SELECT * FROM <sometable> WHERE dateadd(hh, ''' + @tzoffset + '''), END_DATETIME) BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''''

    returns this error

    Conversion failed when converting the varchar value 'SELECT * FROM <sometable> WHERE dateadd(hh, '' to data type int.

    How do I get around this?

  • Hard to tell, but I'd bet one of your @variables is declared as int rather than varchar so the '+' becomes an addition operator rather than a concatenation operator. When concatenating, everything needs to be char or varchar.


    And then again, I might be wrong ...
    David Webb

  • It's as simple as this:

    SET @sql = 'SELECT * FROM <sometable>

    WHERE dateadd(hh, @tzoffset, END_DATETIME) BETWEEN @StartDate AND @EndDate'

    When you work with dynamic SQL you should use parameterised statements for many reasons, and one is exactly that it is simpler.

    The way you run the query is this:

    EXEC sp_executesql @sql, N'@tzoffset smallint, @StartDate datetime, @EndDate datetime',

    @tzoffset, @StartDate, @EndDate

    You can read more about sp_executesql in my article on dynamic SQL:

    http://www.sommarskog.se/dynamic_sql.html#sp_executesql

    By the way, I think a better solution is

    SET @sql = 'SELECT * FROM <sometable>

    WHERE END_DATETIME BETWEEN dateadd(hh, -@tzOffset, @StartDate) AND dateadd(hh, -@tzOffset, @EndDate'

    While longer, the first solution runs the risk that the optimzer will not use any index on END_DATETIME.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • David, Thank you. I will keep that in mind.

    Erland, I think this is exactly the information I needed! I will post back tomorrow when I know for sure it works.

    Rebecca

  • rebecca 79612 (8/29/2013)


    This syntax

    SET @sql = 'SELECT * FROM <sometable> WHERE dateadd(hh, ''' + @tzoffset + '''), END_DATETIME) BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''''

    returns this error

    Conversion failed when converting the varchar value 'SELECT * FROM <sometable> WHERE dateadd(hh, '' to data type int.

    How do I get around this?

    Why do you need this to be dynamic SQL? You're not dynamically changing anything that requires dynamic SQL unless you're changing <sometable> to something else.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Declare all variable used in dynamic query as nvarchar or varchar. It will work for sure.

  • churi.hrushikesh (8/30/2013)


    Declare all variable used in dynamic query as nvarchar or varchar. It will work for sure.

    Better than that, don't use dynamic SQL because it doesn't look like it's even needed here. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Erland,

    Thank you very much. This solved my problem and works on the ACTUAL query I am using and not just the simple version posted on the forum.

    Also, I cannot tell you how many times I have visited (and printed) the article 'The curse and blessings of dynamic SQL' over the last couple of years! Thank you for your effort in writing the article. It has served me well. Obviously I need to visit it a little more. 😉

    Rebecca

  • You may find that the following will work better as the execution plans will more likely be cached, plus it will use an index on END_DATETIME if it exists and will help the query. Your code will require a table or clustered index scan since the DATEADD function will have to applied to every row in the table to determine if the criteria matches or not.

    DECLARE @SQLCmd NVARCHAR(MAX),

    @SQLParam NVARCHAR(MAX),

    @StartDate1 DATETIME,

    @EndFate1 DATETIME,

    @tzoffset1 INT;

    SET @SQLCmd = N'

    SELECT *

    FROM <sometable>

    WHERE

    END_DATETIME between DATEADD(hh, -1 * @tzoffset , @StartDate) and DATEADD(hh, -1 * @tzoffset , @EndDate)

    ';

    SET @SQLParam = N'@StartDate DATETIME, @EndDate DATETIME, @tzoffset INT';

    EXEC sp_executesql @SQLCmd, @SQLParam, @StartDate = @StartDate1, @EndDate = @EndFate1, @tzoffset = @tzoffset1;

  • Lynn,

    :w00t: Ah, yes I see what you mean. Thank you for further insight on this solution!

  • You don't want to use a function against the END_DATETIME column if you can avoid it, since any function will prevent SQL from using the full seek capabilities against that column in any index(es) in which it might appear.

    I think the code below will do SELECT the same rows as your original query, but adjust the code if/as needed to match you need:

    SELECT *

    FROM dbo.tablename

    WHERE

    END_DATETIME >= DATEADD(HOUR, -@tzoffset, @StartDate) AND

    END_DATETIME <= DATEADD(HOUR, -@tzoffset, @EndDate)

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (9/3/2013)


    You don't want to use a function against the END_DATETIME column if you can avoid it, since any function will prevent SQL from using the full seek capabilities against that column in any index(es) in which it might appear.

    I think the code below will do SELECT the same rows as your original query, but adjust the code if/as needed to match you need:

    SELECT *

    FROM dbo.tablename

    WHERE

    END_DATETIME >= DATEADD(HOUR, -@tzoffset, @StartDate) AND

    END_DATETIME <= DATEADD(HOUR, -@tzoffset, @EndDate)

    Same thing I suggested in my post.

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

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