First Date and Last Date of Last Month using SQL

By | January 2, 2014
rx online

In this article we will see how to get the first and last date of past / last month. This is very helpful to generate the previous month reports.

Declare @CurrentDate datetime
Declare @StartDate datetime
Declare @EndDate datetime
set @CurrentDate=getdate()
SELECT @StartDate=<strong>DATEADD</strong>(dd,-(DAY(DATEADD(mm,1,@CurrentDate))-1),DATEADD(mm,-1,@CurrentDate))
SELECT @EndDate=<strong>DATEADD</strong>(s,-1,DATEADD(mm, DATEDIFF(m,0,@CurrentDate),0))
select @StartDate=<strong>convert</strong>(varchar(12),@StartDate,112) ,@EndDate=convert(varchar(12),@EndDate,112)
select @StartDate as FirstDate,@EndDate as LastDate

In above query i have created three variable @CurrentDate ,@StartDate and @EndDate. @CurrentDate is not required then also i have used to set the current date. However, as per requirement if we want to change it we can do it very easily.

You can ignore @CurrentDate variable with getdate() if you don’t want to create this variable. Also, you can create the function to reuse it across project.


1,045 total views, 1 views today

Category: SQL