Deleting data based on system date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting data based on system date

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-10 : 11:55:52

Need help with SQL to retreive records from a table based on a column in the table and system date

Table Name - RequestInfo
ColumnName - ReceivedDate

I would like to retreive all records from the RequestInfo Table where (SystemDate) - (Receiveddate) <= last24months

Thanks

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-10 : 11:59:01

I tried the below SQL and was able to get what I want.Is this the right way

select * from RequestInfo
where datediff(month,receiveddate,getdate()) <=24

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 12:44:48

Not if there's and index on receiveddate

where receiveddate >= dateadd(month,-24,getdate())

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 12:47:35

Actually, even if there isn't an index on receiveddate. Your query has to make a function call for each record. Both will give same results, but mine should give them faster. But you got the gist of what needed to be done!

Jim

Everyday I learn something that somebody else already knew

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-10 : 13:00:34

Thanks

quote:
Originally posted by jimf

Actually, even if there isn't an index on receiveddate. Your query has to make a function call for each record. Both will give same results, but mine should give them faster. But you got the gist of what needed to be done!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources