January 4, 2014 at 12:05 pm
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
January 4, 2014 at 12:41 pm
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
January 4, 2014 at 12:57 pm
Perfect...thanks very much...my eyes were starting to cross
January 4, 2014 at 1:02 pm
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