Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using AND for multiple conditons Expand / Collapse
Author
Message
Posted Saturday, January 04, 2014 12:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 4:58 AM
Points: 14, Visits: 30
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

Post #1527846
Posted Saturday, January 04, 2014 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 14,825, Visits: 27,302
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1527855
Posted Saturday, January 04, 2014 12:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 4:58 AM
Points: 14, Visits: 30
Perfect...thanks very much...my eyes were starting to cross
Post #1527858
Posted Saturday, January 04, 2014 1:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 14,825, Visits: 27,302
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1527860
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse