How to Find First and Last Day of Current Month or Previous Month in SQL ?

Here I will explain how to write query to get or find first and last day of current month or previous month in SQL Server.

Get First and Last Day of Current Month

If you want to get First and last day of current month you need to use DATEADD function and write the query like this

SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),103) AS [Date],

'First Day of Current Month' AS [Type]

UNION

SELECT CONVERT(VARCHAR(10),GETDATE(),103) AS Date_Value, 'Today' AS [Type]

UNION

SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),103) AS [Date],

'Last Day of Current Month' AS [Type]

OUTPUT:

Date	Type
1/10/2012	First Day of Current Month
30/10/2012	Today
31/10/2012	Last Day of Current Month

Get First and Last Day of previous Month

If you want to get First and last day of previous month you need to use DATEADD function and write the query like this

SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,-1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),103) AS [Date],

'First Day of Previous Month' AS [Type]

UNION

SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),103) AS [Date],

'Last Day of Previous Month' AS [Type]

OUTPUT:

Date	Type
1/9/2012	First Day of Previous Month
30/09/2012	Last Day of Previous Month