May 23, 2008 at 8:09 am
I use this to get the date:
DECLARE @date as VARCHAR (10)
SET @date = convert(varchar,getdate(),103)
103 I think that is "DD/MM/YYYY"
And I would like to delete those rows that are or they are more than a week, to maintain the last week's rows only, because the table gets very big, inserting the rows of each day.
DELETE FROM dataBase
WHERE Date < @date - 7 days
Is there a function that controlls if a month has 30, 31, 28 or 29 days? Because, it has to be taken in account.
May 23, 2008 at 8:49 am
Why are you using varchar for your date value? When I have had to do something like this I usually get the max date in the table something like this:
[font="Courier New"]DELETE
FROM
table1
WHERE
date1 < (SELECT DATEADD(DAY, -7, MAX(date1)) FROM table1)
[/font]
I don't know of a function that will return the # of days in a month. You could do something like this in a UDF:
[font="Courier New"]CREATE FUNCTION dbo.fnDaysInMonth
(
@date DATETIME
)
RETURNS tinyint
AS
-- place the body of the function here
BEGIN
DECLARE @days tinyint
IF DATEPART(DAY, @date) <> 1
BEGIN
SET @date = DATEADD(DAY, -DATEPART(DAY, @date) +1, @date)
END
SELECT
@days = DATEPART(DAY, DATEADD(DAY, -1, DATEADD(MONTH, 1, @date)))
RETURN (@days)
END[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 23, 2008 at 9:28 am
thank you for your answer.
I have to take in account the day of today, instead of doing a select of MAX(date) because it's going to be an SP executed by a JOB once a day at night.
May 23, 2008 at 9:40 am
So change it to use getdate() and if you really just want the date which by default will have a time of midnight you can do:
Declare @delete_date datetime
Set @delete_date = Convert(varchar(10), DateAdd(day, -7, getdate()), 103)
date1 < @delete_date
If you run the example today then @delete_date will be '2008-05-23 00:00:00:000'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 23, 2008 at 9:54 am
I have found the solution:
DECLARE @date as VARCHAR (10)
SET @date = convert(varchar, DATEADD (week, -1, getdate()), 103)
DELETE FROM table
WHERE @date > columName
Thanks:D
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply