SQL SERVER – Query to Find First and Last Day of Any month

Following queries 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...

First Day of Current Month

select dateadd(mm,datediff(mm,0,getdate()),0)

Last Day of Current Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate())+1,0))

First Day of Last Month

select dateadd(mm,datediff(mm,0,getdate())-1,0)

Last Day of Last Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate()),0))

First Day of Next Month

select dateadd(mm,datediff(mm,0,getdate())+1,0)

Last Day of Next Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate())+2,0))

Major Date functions in SQL Server are

DateAdd()
DateDiff()
DataPart()
DateName()
Getdate()

 select CONVERT(DATE, dateadd(mm, datediff(mm,0, getdate())+1,-1))

 select CONVERT(DATE, CAST(YEAR(getdate()) AS VARCHAR(4)) + '/' + CAST(MONTH(getdate()) AS VARCHAR(2)) + '/' + '01', 111)