deleting records based on date difference

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

SQL Server 2005 Forums

Transact-SQL (2005)

deleting records based on date difference

Author

Topic

CRP
Starting Member

10 Posts

Posted - 2008-03-27 : 03:13:53

I am having a table where i have the following columns where the date format is dd/mm/yyyy

Purchase Description From_Date To_Date
------------------- ------ --------
Desktop 2/2/2007 2/3/2007
Mouse 2/1/2007 28/1/2007
Laptop 5/1/2008 15/3/2008
Speaker 4/1/2008 21/1/2008

My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?

Please provide me with full stored procedure

Thanx in advance

C.R.P RAJAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 04:28:01

DELETE
FROM Table1
WHERE CAST(To_Date AS DATETIME) - CAST(From_Date AS DATETIME) > 30

I hope this learns you that you should always use PROPER datatypes to store data.
If you store dates, use DATETIME or SMALLDATETIME.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources