How to truncate data in SQL output database to store only last 2 years of data

Hi All,

I have a requirement to load data from a query to a SQL database. The workflow will be executed 5th of every month. We have added a column named Run_Date in the workflow. Run_Date will hold today's date (i.e. workflow execution date.)

Now in output SQL database every month when workflow will be executed the data must be appended. So for that we can select Output options as 'Append Existing'.

Now the problem is the output database should have only data for last 2 years. Example, if workflow will be executed in December 2024 then the output SQL database should truncate all records before December 2022 (i.e. records where Run_Date <= December 2022 will be truncated) and should have only data from January 2023 to December 2024.

Does anybody know can i achieve this.

Gallery

Output

Accepted answers

Felipe_Ribeir0

Hi @deekshaChoudhary

To delete rows with dates <= 24 months ago dynamically, use this piece of query:

DELETE FROM Table
WHERE DateColumn <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-24, 0);

One good option is to use this as pre SQL Statement inside the same output tool that is being used to append the new data.

All comments

acarter881

I believe BETWEEN is what you're looking for.

There should be a way to make the necessary variables for the comparison.

Example:

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Felipe_Ribeir0

Hi @deekshaChoudhary

To delete rows with dates <= 24 months ago dynamically, use this piece of query:

DELETE FROM Table
WHERE DateColumn <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-24, 0);

One good option is to use this as pre SQL Statement inside the same output tool that is being used to append the new data.

deekshaChoudhary

Thanks alot. This worked.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703