Query to Find First and Last Day of Current Month, Next Month and Previous Month in SQL Server


Run the following queries in MS SQL Server to find the First and Last Day of Current Month, Next Month and Previous Month respectively.

DECLARE @CurrentDate DATETIME
SELECT @CurrentDate = GETDATE()

DECLARE @NextMonthDate DATETIME
SELECT @NextMonthDate = DATEADD(mm,1,@CurrentDate)

DECLARE @PrevMonthDate DATETIME
SELECT @PrevMonthDate = DATEADD(mm,-1,@CurrentDate)

SELECT CONVERT(NVARCHAR(10),
DATEADD(dd,-DAY(@CurrentDate)+1,@CurrentDate),103) AS [Date (dd/mm/yyyy)],
'First Day of Current Month' AS [Description]

UNION

SELECT CONVERT(NVARCHAR(10),
DATEADD(dd,-DAY(@NextMonthDate),@NextMonthDate),103) AS [Date (dd/mm/yyyy)],
'Last Day of Current Month' AS [Description]

UNION

SELECT CONVERT(NVARCHAR(10),
DATEADD(dd,-DAY(@NextMonthDate),@NextMonthDate)+1,103) AS [Date (dd/mm/yyyy)],
'First Day of Next Month' AS [Description]

UNION

SELECT CONVERT(NVARCHAR(10),
DATEADD(dd,-DAY(DATEADD(mm,1,@NextMonthDate)),DATEADD(mm,1,@NextMonthDate)),103) AS [Date (dd/mm/yyyy)],
'Last Day of Next Month' AS [Description]

UNION

SELECT CONVERT(NVARCHAR(10),
DATEADD(dd,-DAY(@CurrentDate),@CurrentDate),103) AS [Date (dd/mm/yyyy)],
'Last Day of Previous Month' AS [Description]

UNION

SELECT CONVERT(NVARCHAR(10),
DATEADD(dd,-DAY(@PrevMonthDate)+1, @PrevMonthDate),103) AS [Date (dd/mm/yyyy)],
'First Day of Previous Month' AS [Description]

GO

Query to Find First and Last Day of Current Month in SQL Server