SQL SERVER – Query to Find First and Last Day of Current Month

 Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

DECLARE @mydate DATETIME

SELECT @mydate = GETDATE()

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,'Last Day of Previous Month'UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,'First Day of Current Month' AS Date_Type

UNION

SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' ASDate_Type

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,'Last Day of Current Month'

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,'First Day of Next Month'

GO