DateDiff and 180 Days ago 1

[NickyJay]

Hi all,

I need to pull out only records that havent been updated for 6 months in my DB table. I have a field that automatically records the date a record was updated (DateTime) and i need only those that are 180 days or more from today's date -try as i might i cant get my brain to work it out! Can anyone point me in the right direction?

Thanks!!

 

[PDreyer]

where upddate>dateadd(dd,-180,getdate())

 

[PDreyer]

where upddate<=dateadd(dd,-180,getdate())

 

[NickyJay]

Hiya,

Thanks for responding, i tried both and got the following - the first one returned all records that had an entry in the Update field, the second returned nothing and i purposly set a record to be over 6months old....

Should i definatly be using DATEADD??

Nicola

 

[nastoski]

Hi,
maybe you can try with this one
...
WHERE DATEDIFF(DAY, update_dt, GETDATE()) > 180

Hope this help

Regards,

Igor

 

[NickyJay]

Hiya, managed to see why PDreyer's suggestion didnt work - i had another filter i had forgotten about in my query - the 2nd suggestion of where upddate<=dateadd(dd,-180,getdate()) worked perfectly, thanks guys!!

Nicola

 

[ESquared]

Don't use datediff because then the function has to be performed on every row. Using the original method given you it only has to be performed ONCE, then a simple less-than comparison made. Much more efficient.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]