I am completely new to sql. Tell me, please, is it possible to make it so that all dates are deleted more than the current month and less than a certain date? All months in the format of the words "June". My construction is clearly incorrect:

delete from www where [Источник]='База' and ([год]<=2017) or ([год]=2018 and [месяц] <= DATENAME(month, GETDATE()))

Initially it was like this:

 delete from www where [Источник]='База' and ([год]=2016 and [месяц] not in ('Май','Июнь','Июль','Август','Сентябрь','Октябрь','Ноябрь','Декабрь')) or ([год]=2017 and [месяц] not in ('Январь','Февраль','Март','Апрель','Май','Июнь','Июль','Август','Сентябрь','Октябрь','Ноябрь','Декабрь')) or ([год]=2018 and [месяц] not in ('Январь','Февраль','Март','Апрель','Май','Июнь','Июль','Август','Сентябрь','Октябрь','Ноябрь')

The correct solution (another column was chosen for comparison):

  delete from www where [Источник]='База' and ([Дата] < CONVERT(datetime, '2016-05-01') or [Дата] > eomonth(getdate()))
  • And what does all the dates mean more than the current month , in the example given by you, you are just trying to delete all the dates less than the current month. Please complete your question with the table schema. - Nick Proskuryakov
  • Microsoft SQL Server MS - user316970
  • You are right, I need to delete everything except all the months from the beginning of the current year - user316970
  • It is necessary to automate in order not to add a month each time, preferably a year, but it is not now. - user316970
  • 3
    With such a data model, your still waiting for a lot of unpleasant surprises and unnecessary complications. Replace two fields with one [дата] field and compare dates with dates. - MaxU

1 answer 1

There are several options for solving your problem:

First , simply delete everything that is less than the current date minus the month, is solved as follows:

 DELETE FROM [MyTable] WHERE [FullDate] < cast(dateadd(MONTH,-1,GETDATE()) as date)

At the output we have the data (today 11/23/2018) from 10.23.2018 to 11.24.2018.

Second , delete all where the date is less than the first day of the current month:

 DELETE FROM #MyTable WHERE [FullDate] < cast(DATEADD(month, DATEDIFF(month,0, GETDATE()),0) as date)

At the output we only have data for the current month.

Where [MyTable] is the name of the table, [FullDate] is the name of the field in which you have stored the date in the view 2018-11-01.