Current and Previous Week, Month, Quarter, Year in SQL Server

This blog defines current and previous  Week, Month, Quarter, Year in SQL Server.

DECLARE @DateFilterType NVARCHAR(MAX)

--set @DateFilterType = 'CurrentWeek'

--set @DateFilterType = 'CurrentMonth'
--set @DateFilterType = 'CurrentQuarter'

--set @DateFilterType = 'CurrentYear'

--set @DateFilterType = 'PreviousWeek'

--set @DateFilterType = 'PreviousMonth'

--set @DateFilterType = 'PreviousQuarter'

--set @DateFilterType = 'PreviousYear'
 

SELECT

CASE
WHEN @DateFilterType = 'CurrentWeek'    THEN DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0)

WHEN @DateFilterType = 'CurrentMonth'   THEN DATEADD(M, DATEDIFF(M,0,GETDATE()),0)

WHEN @DateFilterType = 'CurrentQuarter' THEN DATEADD(QQ, DATEDIFF(QQ,0,GETDATE()),0)

WHEN @DateFilterType = 'CurrentYear'   THEN DATEADD(YY, DATEDIFF(YY,0,GETDATE()),0)
  
WHEN @DateFilterType = 'PreviousWeek'   THEN DATEADD(day, -7 ,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

WHEN @DateFilterType = 'PreviousMonth'   THEN DATEADD(month, -1, DATEADD(M, DATEDIFF(M,0,GETDATE()),0))

WHEN @DateFilterType = 'PreviousQuarter' THEN DATEADD(QQ, DATEDIFF(QQ,0, Dateadd(ms,-6,Dateadd(qq, Datediff(qq,0,GetDate()), 0))),0)

WHEN @DateFilterType = 'PreviousYear'    THEN DATEADD(YY, DATEDIFF(YY,0,DATEADD(year,-1,GETDATE())),0)

END
AS [StartDate]

,CASE
WHEN @DateFilterType = 'CurrentWeek'     THEN  GETDATE()


WHEN @DateFilterType = 'CurrentMonth'    THEN  GETDATE()

WHEN @DateFilterType = 'CurrentQuarter' THEN   GETDATE()

WHEN @DateFilterType = 'CurrentYear'   THEN   GETDATE()

WHEN
@DateFilterType = 'PreviousWeek'   THEN   DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0)
WHEN @DateFilterType = 'PreviousMonth'   THEN  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

WHEN @DateFilterType = 'PreviousQuarter' THEN  Dateadd(ms,-6,Dateadd(qq, Datediff(qq,0,GetDate()), 0))

WHEN @DateFilterType = 'PreviousYear' THEN DATEADD(s,-1,DATEADD(YY, DATEDIFF(YY,0,DATEADD(YY, DATEDIFF(YY,0,DATEADD(year,-1,GETDATE())),0))+1,0))

END AS [EndDate]
Ebook Download
View all
Learn
View all