Delete records older than 12 months but the date is in YYYYMM format

Question

Hi,

I have a table that gets updated with fresh data every month
I am trying to delete records which are older than 12 months (1 year) from my table.(automate through SP to keep only rolling 12 months data), but the problem is the field 'LoadedDate' is in YYYYMM (201808)(varchar) format.(This field is actually the date to tell which month's data it is..... not like the date it was loaded in the table as the data insert is pretty random throughout the months)

Thanks in advance

Answer 1

If I get this right:

DELETE tbl
WHERE LoadedDate < (SELECT convert(char(6), MAX(convert(int, LoadedDate)) - 100) FROM tbl

That is, convert the highest value of LoadedDate to integer, deduct 100 to go back one year.

(This is a little shortcut, and would not work well if the limit was 15 or 18 months.)

Answer 2

Hi @jennifer zen ,

I think you are looking for

select CONVERT(varchar(6), DATEADD(month, -12, GETDATE()), 112)

Here, GETDATE() will give you today's date and then DATEADD() will deduct 12 months from today's date and give you year and month as YYYYMM. You can use the result then your Delete Query.

Hope this will help. Thanks!

Answer 3

DECLARE @MyTable TABLE (
    LoadedDate varchar(6)
);
INSERT INTO @MyTable VALUES
('201609'), ('201709'),('201809'), ('201909'), ('202009');

DECLARE @numberOfMonthesDelayed int = 3;
DECLARE @startMonthYearToBeDeleted varchar(20);
SET @startMonthYearToBeDeleted = LEFT(CONVERT(varchar, DATEADD(month, -(12 + @numberOfMonthesDelayed), GetDate()), 112), 6);

DELETE @MyTable WHERE LoadedDate <= @startMonthYearToBeDeleted;
SELECT * FROM @MyTable;

Answer 4

Hi All,

All the answers worked :)

Ass suggested by @Guoxiong , @NasreenAkter-2040, @SM

I can use get date() and add three months delay as making it to -15 or as @Erland Sommarskog suggested..

convert to integer and delete them ( as my limit is 12 months) ...

As I couldn't mark them all as optional answers, have just upvoted your comment.

Thanks a lot...appreciate your help :)

Answer 5

DECLARE @MyTable TABLE (  
	LoadedDate varchar(6)  
);  
INSERT INTO @MyTable VALUES  
('201609'), ('201709'),('201809'), ('201909'), ('202009');  
  
DELETE @MyTable  
WHERE LoadedDate <= '201808';  
  
SELECT * FROM @MyTable;