MSSQL code to find all records within 1 year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

danmarc92 - 02 Jul 2008 16:27 GMT

Does anybody have a good code for defining records to those within 1 year. We
have a field (closingdate) and so we need to show all records for up to 1
year after their closingdate.
Thanks

Reply to this Message

Eric Isaacs - 02 Jul 2008 16:42 GMT

WHERE tablename.closingdate >= DATEADD(d, -366, CURRENT_TIMESTAMP)

-Eric Isaacs

Reply to this Message

Plamen Ratchev - 02 Jul 2008 16:50 GMT

Here is one way:

SELECT <columns>
FROM Foo
WHERE closingdate >= DATEADD(YEAR, -1,
DATEADD(DAY, DATEDIFF(DAY, 0,
CURRENT_TIMESTAMP), 0));

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

danmarc92 - 03 Jul 2008 09:33 GMT

Thanks for your help, the querues given do work but I actually need to
restrict the records to a year from the closingdate, not a year from current
date, does anyone know how to achieve this?.
Thanks

> Here is one way:
>
[quoted text clipped - 8 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 03 Jul 2008 13:14 GMT

Please provide table structure, inserts for sample data and expected
results. It is not clear from your notes what you really need.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

danmarc92 - 03 Jul 2008 13:46 GMT

select *
from tblpatientrecords
where consentdate >= appointdate
AND consentdate <= DATEADD(d, -366, closingdate)

Therefore there are effectively 3 different dates that the table stores,
startingdate, appointdate, and closingdate.
I need to show all the info in the table based on records that the
consentdate is on/after the appointdate, and they also have to be records all
records where the consentdateis within 1 year of the closingdate.

> Please provide table structure, inserts for sample data and expected
> results. It is not clear from your notes what you really need.
[quoted text clipped - 3 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 03 Jul 2008 14:03 GMT

What do you get when you run the code you posted? Does it return the result
set you expect?

Perhaps you need this:

SELECT consentdate, appointdate, closingdate
FROM tblpatientrecords
WHERE consentdate >= appointdate
AND consentdate <= DATEADD(YEAR, 1, closingdate);

If not then please post sample data and expected results.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message