March 27, 2008 at 1:04 am
I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase DescriptionFrom_DateTo_Date
------------------------- --------
Desktop2/2/20072/3/2007
Mouse2/1/200728/1/2007
Laptop5/1/200815/3/2008
Speaker4/1/200821/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
March 27, 2008 at 1:54 am
If your date columns are defined datetime you're OK with this proc.
create proc usp_thedeletes
as
begin
set nocount on
delete
from yourtable
where datediff(d, From_Date, To_Date) > 30
end
Else, don't come complaining that the columns contain invalid dates !
If your date columns are defined (var)char, you'll have to convert them to datetime using
datediff(d, convert(datetime, From_Date, 103 ) , convert(datetime, To_Date, 103 ) ) > 30
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2008 at 2:34 am
Also asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99743
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply