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
<code>
    Monday OF the CURRENT Week :
<code lang="sql">
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