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]