Delete dates that arent end of month

Transact-SQL
1

Hi,

Is it possible to delete data from a table where the dates aren't the end of the month? Dates can come from any month at one time. If so how would it be possible

2

DELETE FROM table_name WHERE date_column <> DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, date_column) + 1, 0))

1 Like
3

thanks will try that

4

EOMOTNH() is also a nice function.

EOMONTH (Transact-SQL) - SQL Server | Microsoft Learn

1 Like
5

hi

hope this helps

another way to do it

create sample data

drop table #Data

create table #Data ( dt date )

insert into #Data select '1990-07-21'
insert into #Data select '2005-10-31'
insert into #Data select '2008-03-23'
insert into #Data select '2001-09-11'
insert into #Data select '2001-09-30'

delete from #Data where month(dt) = month(dateadd(d,1,dt))

image

1 Like
6

Assuming you're on a recent enough version of SQL Server to have it available. I don't like to assume that (even if it is only SQL 2012 (IIRC)).