Tuesday, July 7, 2020

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(),-1as  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

No comments:

Post a Comment