July 23, 2025 at 1:11 pm
Hi
We have databases with data stored for over 20 years, this is required by law. We though want to move data to a Archive database where is can be accessed at any time, at times directly using a view linking the 2 databases and at other times manually having to select the data using a synonym only.
I have created the scripts to copy the data on monthly basis selecting the oldest month and then copying it to archive and deleting it from the Production DB. These are huge amount of data including tables linked with Foreign keys.
The following script works on the month selection I then use to insert to Archive and delete on production.
DECLARE @Period int
SELECT @Period = min(PERIOD)
from DBName.dbo.SERVICE
Print @Period
The results would be for example 201506- in other words PERIOD value on SERVICE table has values with YYYYMM
My problem is when adding a limit to not delete data for the last 5 years, so currently I would not want the script to run if the min(PERIOD) is 202007
For this I tried adding this where clause below. The select statement in the where clause works but if added to the above script the
Print @Period is not giving me any result. I am thinking that I am overthinking this or just missing something stupid
where @Period < (select LEFT(CONVERT(varchar(6),DATEADD(yyyy,-5, getdate()),112),6))
Any help would be appreciated
July 23, 2025 at 1:23 pm
Why not make @Period an actual date and then use date math on the value there? Seems like it'd be easier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2025 at 1:27 pm
I am trying to link @Period to the PERIOD column on the table directly
July 23, 2025 at 4:41 pm
How about doing it a little differently and instead of:
where @Period < (select LEFT(CONVERT(varchar(6),DATEADD(yyyy,-5, getdate()),112),6))
you first store the yyyymm in a variable with something like:
DECLARE @FiveYearAgo INT
SELECT @FiveYearAgo = ((DATEPART(Year, GETDATE())*100) + DATEPART(Month,GetDATE())) - 500
SELECT @FiveYearAgo
Not a fan of the magic number (500) but that is how you do the 5 year. Then do where @Period < @FiveYearAgo instead.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 29, 2025 at 3:29 pm
It is still not working, I tried something similar, I think the issue is that @Period is an integer and @FiveYearAgo is not so if I do the where clause and then print @Period it does not give me a result
Not sure what you mean that @FiveYearAgo is not an INT, you are explicitly defining it as an INT. It is 100% an INT.
What happens if you select the MIN PERIOD (ignore the where clause) and @FiveYearAgo? My guess is you have no data where period is less than @FiveYearAgo OR Period isn't an INT...
EDIT - disregard the above. It is not related. The problem is you are comparing NULL to @FiveYearAgo. Fix your WHERE clause as @period is not defined when that part of the query starts. It SHOULD be PERIOD < @FiveYearAgo, not @PERIOD < @FiveYearAgo
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 4, 2025 at 1:40 pm
Thanks, I did have to change Where cause, link it to PERIOD <= @FiveYearAgo
Sorted Thanks guys, I overthought/overcomplicated this in my mind
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply