SQL Server 2012 & above
DECLARE @DATE AS DATETIME = GETDATE()
SELECT DATEADD(DD, - (DAY(EOMONTH(@DATE)) -1 ), EOMONTH(@DATE)) 'FIRST DAY OF CURRENT MONTH' -- 2016-04-01
SELECT EOMONTH(@DATE) 'LAST DAY OF CURRENT MONTH' -- 2016-04-30
LET’S EXPLORE IT
How to calculate the First Day of Current Month
Step 1:- GET THE LAST DAY OF CURRENT MONTH
SELECT EOMONTH(@DATE) -- 2016-04-30
Step 2:- GET THE CURRENT MONTH NUMBER OF DAY
SELECT DAY(EOMONTH(@DATE)) -- 30
Step 3:- REDUCE THE CURRENT MONTH NUMBER OF DAY WITH 1 DAY
SELECT (DAY(EOMONTH(@DATE)) - 1) -- 29
Step 4:- FINALLY ADD THE REDUCED CURRENT MONTH NUMBER OF DAY IN DATE
SELECT DATEADD(DD, - (DAY(EOMONTH(@DATE)) -1 ), EOMONTH(@DATE)) 'FIRST DAY OF CURRENT MONTH'
Below SQL Server 2012
DECLARE @DATE DATETIME
SET @DATE = GETDATE()
SELECT DATEADD(MM, DATEDIFF(MONTH,0,@DATE), 0)'FIRST DAY OF CURRENT MONTH' -- 2016-04-01
SELECT DATEADD(MM, (DATEDIFF(MONTH,0,@DATE) + 1 ), -1)'LAST DAY OF CURRENT MONTH' -- 2016-04-30
LET’S EXPLORE IT
How to calculate the First Day of Current Month
Step 1:- GET THE CURRENT DATE
SELECT GETDATE() -- 2016-04-01
Step 2:- GET THE TOTAL NUMBER OF MONTHS BETWEEN TWO DATES. 0 MEANS DEFAULT
DATE OF SQL SERVER I.E. '1900-01-01'
SELECT DATEDIFF(MM, 0 ,GETDATE()) – 1395
Step 3:- FINALLY ADD THE TOTAL NUMBER OF MONTHS IN DATE. 0 MEANS CURRENT
DATE FIRST DAY
SELECT DATEADD(MM, DATEDIFF(MONTH,0,@DATE), 0)'FIRST DAY OF CURRENT
MONTH' -- 2016-04-01
How to calculate the Last Day of Current Month
Step 1:- GET THE CURRENT DATE
SELECT GETDATE() -- 2016-04-01
Step 2:- GET THE TOTAL NUMBER OF MONTHS BETWEEN TWO DATES. 0 MEANS DEFAULT
DATE OF SQL SERVER I.E. '1900-01-01'
SELECT DATEDIFF(MM, 0 ,GETDATE()) – 1395
Step 3:- ADD THE 1 MONTH IN THE TOTAL NUMBER OF MONTHS, TO GET THE NEXT
MONTH
SELECT (DATEDIFF(MM, 0 ,GETDATE()) + 1) – 1396
Step 4:- FINALLY ADD THE TOTAL NUMBER OF MONTHS IN DATE. -1 MEANS REDUCING
THE NEXT MONTH DATE WITH 1 DAY
SELECT DATEADD(MM, (DATEDIFF(MONTH,0,@DATE) + 1 ), -1)'LAST DAY OF CURRENT MONTH' -- 2016-04-30
NOTE:-
EOMONTH()
Returns the last day of the month that contains the specified
date, with an optional offset.
DECLARE @DATE DATETIME = GETDATE()
SELECT EOMONTH(@DATE) AS 'CURRENT MONTH' -- 2016-04-30
SELECT EOMONTH(@DATE, 1) AS 'NEXT MONTH' -- 2016-05-31
SELECT EOMONTH(@DATE,-1) AS 'LAST MONTH' -- 2016-03-31
