Calculate last day of month using sql server

There are couple of ways to calculate last day of a given month.

SQL Server 2012 on wards there is inbuilt function find the last day of month.

Let's see different ways with example:

Using SQL Server in built function.

select EOMONTH(getdate(),0) as curr_month

output:

curr_month

2020-07-31

select EOMONTH(getdate(),-1) as  Prev_month

output:

Prev_month

2020-06-30

Note: 0,-1, -2 can be used to navigate among months.

Let's see how to find last day of month with out using the function

EX1:

select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,0,getdate())),-1) as curr_month

EX2: (best approach)

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, getdate()) +1, 0)) as curr_month

curr_month

2020-07-31 00:00:00.000

Ex1:

select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,-1,getdate())),-1) as Prev_month

Ex2:

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, getdate()) , 0)) as Prev_month

Prev_month

2020-06-30 00:00:00.000

Highlighted 0,-1 is being used to navigate among months

The above script can be converted to t-sql scripts

Declare @date datetime

set @date='2020-07-21'

 select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,0,@date)),-1) as curr_month

or

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) +1, 0)) as curr_month

curr_month

2020-07-31 00:00:00.000