December 19, 2011 at 4:45 am
Hello,
need to handle below scenario:
user should get data if the eneterd date is within the range of current date to past 90 days.
and i m using below query for this purpose.
SELECT * From EBBReport WHERE IsCurrent=0 AND
(DATEPART(y,EffDate) = DATEPART(y,@Effddate))
AND EffDate > DATEADD(dd, -90, getdate())
I'm not sure how to validate this criteria.
1) user should not see data if the entered date is not within the above range
2)user should not see data if the entered date is future date.
Also i would like to make clear that i have to handle this in SQL QUERY only(no javascript, no .net code)
December 19, 2011 at 5:15 am
Assuming that effdate is the column under consideration here:
EffDate BETWEEN DATEADD(dd, -90, getdate()) AND getdate()
If that's not correct, please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 3:38 am
Hi,
I have managed to create below query:
SELECT * From Report WHERE IsCurrent=0 AND (DATEPART(y,EffDate) = DATEPART(y,@EffDate)) AND EffDate > DATEADD(dd, -90, getdate())
Let me know if there is any way to optimize this query.
December 20, 2011 at 3:43 am
That doesn't satisfy the 2nd of your requirements
2)user should not see data if the entered date is future date.
With the query that you've written, rows with dates in the future will appear, as long as they are in the current year (so today, 20th December, rows for 30 December would show up)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply