Using the DATEDIFF

Asked

Viewed 85 times

-1

Guys, how do I make a condition with DATEDIFF deletes data from a table in 12 in 12 months?

Example:

You are in June 2020 - Then it erases everything before 01/06/2019.

Another example:

You are in December 2021 - Then it erases everything before 01/12/2020.

I was testing it just to see what happens, but it didn’t work..:

Select * from rvr_tb Where ano_mes / 100 < dateadd(yyyy, +1, getdate())

But you end up reading 202104, for example, but I want to show only those you have in column 202004. I can’t define this, because this data changes.

sql sql-server

  • And what it means ano_mes / 100? What kind of data ano_mes? If you wish 1 year ago should not use dateadd(yyyy, -1, getdate())?

  • @anonimo then, one thing I found wrong and is making it difficult for me to delete the data from the previous year, the ano_mes column is as int. We know that if 202004 is equal to 04/2020, but if I will use this field in functions like date, it no longer works. I don’t know how to convert and I don’t know what I can do.

  • Try using the function DATEFROMPARTS ( year, month, day ) to create a date field from 3 integers. Or create an integer like (YEAR(GETDATE()) - 1)*100 + MONTH(GETDATE()).

  • @anonymity but not the 'day' part is only year and month

  • If you want to work with the date type use, for example, 01 for the day.

  • @anonymity does not work using DATEFROMPARTS

  • SELECT DATEFROMPARTS(2020, 06, 01); results 2020-06-01.

  • Important you [Dit] your question and explain objectively and punctually the difficulty found, accompanied by a [mcve] of the problem and attempt to solve. To better understand and enjoy the site is worth reading the Stack Overflow Survival Guide in English.

Show 3 more comments

1 answer

0


Try

-- código #1 v2
declare @UmAno int;
set @UmAno= cast (convert (char(6), dateadd (year, -1, current_timestamp), 112) as int);

DELETE 
  from rvr_tb 
  where ano_mes < @UmAno;

The advantage of the above code is that it is sargable, which enables optimal performance if there is an index by the ano_mes column.

About deleting line set I suggest reading the article Delete row set in huge tables, where care is described when deleting lines without interfering with other processes.

Here is the calculation of @Umano, step by step:

-- código #2 v2
SELECT dateadd (year, -1, current_timestamp),
       convert (char(6), dateadd (year, -1, current_timestamp), 112),
       cast (convert (char(6), dateadd (year, -1, current_timestamp), 112) as int);

Note that there is one last conversion to the data type integer, in order to prevent automatic data type conversion from occurring in the WHERE clause. Depending on the context, automatic data type conversion can generate wrong results or delay in processing. Details in the article The dangers of automatic data type conversion.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.