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

[deekshaChoudhary]

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. 

[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 · Accepted solution]

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. :)