“How to calculate the first date of the current, previous & next month” ? I have seen this question many times in different blogs and there are multiple approaches that we have been following to find it.
For Example
--SQL Server 2005/2008 DECLARE @DATE DATETIME SET @DATE='2012-09-10' --First date of the Previous Month SELECT CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)-1,0),120) AS [Previous Month] --First date of the Current Month SELECT CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE),0),120) AS [Current Month] --First date of the Next Month SELECT CONVERT(VARCHAR(10),DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0),120) AS [Next Month] Previous Month -------------- 2012-08-01 (1 row(s) affected) Current Month ------------- 2012-09-01 (1 row(s) affected) Next Month ---------- 2012-10-01 (1 row(s) affected)
In SQL Server 2012, we can calculate it with the help of a new built in function namely EOMONTH.
Example-1 :
In this example, I calculated the last date of the previous month and later added one more day to it to make the first day of the current month.
Declare @Date as varchar(10) ='2012-09-10' --First date of the Current Month Select DateAdd(day,1,EOMONTH(Getdate(),-1)) as [Current Month] Current Month ------------- 2012-09-01 (1 row(s) affected)
Example-2 : FOMONTH
In this example, we will create a function FOMONTH and will use it same as EOMONTH in SQL server 2012.
CREATE Function [dbo].[FOMONTH]
(@start_date date, @month_to_add int=0)
Returns Date
AS
BEGIN
RETURN DATEADD(DAY,1,EOMONTH(@start_date,@month_to_add-1))
END
GO
--First day of the Previous Month
Select dbo.[FOMONTH] (Getdate(),-1) AS [Previous Month]
--First day of the Current Month
Select dbo.[FOMONTH] ('2012-09-10',0) AS [Current Month]
--First day of the Next Month
Select dbo.[FOMONTH] (Getdate(),1) AS [Next Month]
--First day of the Current Month WITH DEFAULT VALUE
Select dbo.[FOMONTH] (Getdate(),DEFAULT) AS [Current Month]
Previous Month
--------------
2012-08-01
(1 row(s) affected)
Current Month
-------------
2012-09-01
(1 row(s) affected)
Next Month
----------
2012-10-01
(1 row(s) affected)
Current Month
-------------
2012-09-01
(1 row(s) affected)
SQL Server Portal 