Mahmoud ElMansy: knowledge meant to be free

SQL Get First And Last Day of a week-Month-Quarter-Year

In many reports we may need to get data periodically or within specified date interval
so we need a method to calculate the end and start of months, weeks or days.
In this post we will discuss some of this methods through the following examples.

First: Get First day for any month .
we can do this by concatenating year + month +”01″ as a fixed value

DECLARE @CalculatedDate DATETIME
Declare @InputDate DATETIME=getdate();
SET @CalculatedDate = CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01'
select @CalculatedDate as [First Day of Month]

Second: Get last day for any month .

DECLARE @CalculatedDate DATETIME
Declare @pInputDate DATETIME=getdate();
SET @CalculatedDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5)))- (DAY(@pInputDate) - 1) AS DATETIME)
SET @CalculatedDate = DATEADD(DD, -1, DATEADD(M, 1, @CalculatedDate))
select @CalculatedDate [Last Day of Month]

Now we will make some common operations for datetime.
First Day of Month :

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as [First Day of Month]
--Result :2011-05-01 00:00:00.000

First Day of Last Month :

select DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0) as [First Day of Last Month]
--Result :2011-04-01 00:00:00.000

Operation For weeks :
first we need to set the start date of the week
we will set the first day of the week to 7 means (default, U.S. English) Sunday

set DATEFIRST 7

Monday of the Current Week :

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) as [Monday of the Current Week]
--Result :2011-05-23 00:00:00.000

Sunday of the Current Week :

select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate()) as [Sundday of the Current Week]
--Result :2011-05-22 10:59:56.217

First Day of the Year :

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) as [First Day of the Year]
--Result :2011-01-01 00:00:00.000

First Day of the Quarter :

select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as [First Day of the Quarter]
--Result :2011-04-01 00:00:00.000

Midnight for the Current Day :

select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) as [Midnight for the Current Day]
--Result :2011-05-25 00:00:00.000

Last Day of Prior Month :

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)) as [Last Day of Prior Month]
--Result :2011-04-30 23:59:59.997

Last Day of Prior Year :

select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)) [Last Day of Prior Year]
--Result :2010-12-31 23:59:59.997

Last Day of Current Month :

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)) as [Last Day of Current Month]
--Result :2011-05-31 23:59:59.997

Last Day of Current Year

select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0)) as [Last Day of Current Year]
--Result :2011-12-31 23:59:59.997

First Monday of the Month :

select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0) as [First Monday of the Month]
--Result :2011-05-02 00:00:00.000