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
CASEWHEN @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]

SubashPosted Mar 16, 2017, 9:02 PM
Very nice share sir thank you
Gowtham RajamanickamPosted Apr 11, 2016, 2:45 AM
good one...