Using AND for multiple conditons

  • I'm using asp.net to querry a sql 2008 data base.

    The following statement:

    SelectCommand="SELECT [displaydate], [shortname] FROM [taxevents] WHERE (([target] = @target) AND ([startdate] <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))) ORDER BY [displaydate]"

    Gives me a list of all topics [target] that should be displayed on or after today [startdate]. This works fine.

    I'm trying to add an additional AND statement such that in adddtion to the above it also drops topics off the list after a certain date [enddate]. I tried the following:

    SelectCommand="SELECT [displaydate], [shortname] FROM [taxevents] WHERE (([target] = @target) AND ([startdate] <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()) AND ([enddate] >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))))) ORDER BY [displaydate]"

    but it gives me a syntax error at AND (which AND?) but I cannot figure out the problem. I think I have an equal number of ( ) but not sure what the issue is.

    The result I'm seeking is:

    Give me a list of events based on the topic chosen topics [target] that are permited to be viewed today that have a start date before today and that have not yet reached the end date.

    Any help would be apprciated. Thanks

  • You were missing a parenthesis after the first date operation. You don't need all those parentheses. You can have it just like this:

    SELECT [displaydate],

    [shortname]

    FROM [taxevents]

    WHERE [target] = @target

    AND [startdate] <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

    AND [enddate] >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

    ORDER BY [displaydate];

    It'll make for much more clear code.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Perfect...thanks very much...my eyes were starting to cross

  • Not a problem.

    Just so you know, I cheated in figuring out what was up. I used Red Gate SQL Prompt. It's a software my company makes. It's invaluable when working with T-SQL.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

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