SQL code needed for the WHERE clause..

Question

SQL code needed for the WHERE clause..

Tamrak

SSC Eights!

Points: 902
More actions
July 1, 2024 at 11:31 pm

#4432841

I have a table with data appending on a daily basis. We plan to keep the set of data in this table for one full year and delete everything after August 1st of every year.
I am stuck with the query logic to do this. The situations are like this:
If today's date is August 1st, regardless of the year, delete data from the entire table. Or else, do nothing and keep appending the data. The getdate() can be used as well but needs to round down the time to midnight of August the 1st.
Any suggestions? Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3630 More actions · Answer 1

Assuming you delete/append once per day, can you truncate the table if today is the 1st of August? This is set to truncate on the 2nd of July for testing. If the code will run multiple times per day then I would change it to delete where DateTimeColumn < TargetDate.

DROP TABLE IF EXISTS dbo.AppendData
CREATE TABLE dbo.AppendData (Col1 INT, Col2 INT, DateTimeColumn DATETIME)
GO

DECLARE @Today DATE,  
        @TargetDate DATE

SET @Today = CAST(GETDATE() AS DATE)
SET @TargetDate = DATEFROMPARTS(DATEPART(YEAR,GETDATE()),7,2)

IF @Today = @TargetDate
  BEGIN
    TRUNCATE TABLE dbo.AppendData
    --OR
    DELETE  dbo.AppendData
    WHERE DateTimeColumn < CAST(@TargetDate AS DATETIME)
  END

GO
DROP TABLE IF EXISTS dbo.AppendData

Jeff Moden SSC Guru Points: 1003999 More actions · Answer 2

I wouldn't use DELETE on a year old table. That'll be a lot to delete and it'll cause a "log file explosion".

I probably also wouldn't do a whole year at a time but that might just be me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken McKelvey SSCoach Points: 18807 More actions · Answer 3

If you are dealing with large amounts of transactional data you may also want to look at table partitioning or partitioned views.