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
No comments:
Post a Comment