November 11, 2005 at 2:08 pm
I am trying to return rows that are 7 days past due.
I tried the following:
select lname, fname, review_date
where datediff(d, review_date, getdate()-7)< datediff(d,review_date,getdate())
I thought it was working until I noticed the date of 11-7-05 which is not 7 days past due.
Any help will be greatly appreciated
November 11, 2005 at 2:24 pm
Where review_date < dateadd(d, -7, getdate())
November 11, 2005 at 2:37 pm
SET NOCOUNT ON
DECLARE @myTable TABLE
(
lname VARCHAR(10),
fname VARCHAR(10),
review_date DATETIME
)
INSERT @myTable
SELECT 'lname1', 'fname1', DATEADD(DAY, -11, GETDATE()) UNION
SELECT 'lname3', 'fname3', DATEADD(DAY, -10, GETDATE()) UNION
SELECT 'lname4', 'fname4', DATEADD(DAY, -9, GETDATE()) UNION
SELECT 'lname5', 'fname5', DATEADD(DAY, -8, GETDATE()) UNION
SELECT 'lname6', 'fname6', DATEADD(DAY, -7, GETDATE()) UNION
SELECT 'lname7', 'fname7', DATEADD(DAY, -6, GETDATE()) UNION
SELECT 'lname8', 'fname8', DATEADD(DAY, -5, GETDATE()) UNION
SELECT 'lname9', 'fname9', DATEADD(DAY, -4, GETDATE()) UNION
SELECT 'lname10', 'fname10', DATEADD(DAY, -3, GETDATE()) UNION
SELECT 'lname11', 'fname11', DATEADD(DAY, -2, GETDATE()) UNION
SELECT 'lname12', 'fname12', DATEADD(DAY, -1, GETDATE()) UNION
SELECT 'lname13', 'fname13', GETDATE()
SET NOCOUNT OFF
/* your query */
select lname, fname, review_date
from @myTable
where datediff(d, review_date, getdate()-7)< datediff(d,review_date,getdate())
order by review_date
/* correct query */
SELECT lname, fname, review_date
FROM
@myTable
WHERE
DATEDIFF(DAY, review_date, GETDATE()) >= 7
order by review_date
Regards,
gova
November 11, 2005 at 4:17 pm
That worked Thanks much
November 13, 2005 at 2:43 pm
Don't use this option:
WHERE
DATEDIFF(DAY, review_date, GETDATE()) >= 7
It eliminates index on review_date and causes table (or clustered index) scan.
Really bad for performance.
First option is much better.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply