Dec 15, 2014

How to get first & last day of the previous month

Below given script may help you.

This query will return first day of the previous month


SELECT CONVERT(DATE,DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-1, 0))
This query will return last day of the previous month
SELECT CONVERT(DATE,DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) , 0)))

To get first and last day of the current month use following query


SELECT CONVERT(DATE,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)) AS FirstDay

SELECT CONVERT(DATE,DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE())+1 , 0))) AS LastDay

Useful link
DATEADD function in SQL Server