USP_RESOLVEDATEFILTER
Sets date values based on a enum passed in.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DATEFILTER | tinyint | IN | |
| @STARTDATE | datetime | INOUT | |
| @ENDDATE | datetime | INOUT | |
| @ASOFDATE | datetime | IN |
Definition
Copy
CREATE procedure [dbo].[USP_RESOLVEDATEFILTER]
@DATEFILTER tinyint,
@STARTDATE datetime output,
@ENDDATE datetime output,
@ASOFDATE datetime = NULL --Allows you to resolve a relative range such as This Year in terms of an AsOf date instead of the current date.
as
set nocount on;
if @DATEFILTER=0
-- specific dates, do not alter the dates passed in
return 0;
if @ASOFDATE is null set @ASOFDATE = getdate();
set @STARTDATE = @ASOFDATE;
set @ENDDATE = @STARTDATE;
-- @DATEFILTER=1 is for today only
if @DATEFILTER=2 begin
-- Next 7 days
set @ENDDATE = dateadd(day,7,@ENDDATE);
end else if @DATEFILTER=3 begin
-- Last 7 days
set @STARTDATE = dateadd(day,-7,@STARTDATE);
end else if @DATEFILTER=4 begin
-- Next 30 days
set @ENDDATE = dateadd(day,30,@ENDDATE);
end else if @DATEFILTER=5 begin
-- Last 30 days
set @STARTDATE = dateadd(day,-30,@STARTDATE);
end else if @DATEFILTER=6 begin
-- Current month
set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=7 begin
-- Previous month
set @STARTDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=8 begin
-- Next 12 months
set @ENDDATE = dateadd(year,1,@ENDDATE);
end else if @DATEFILTER=9 begin
-- Last 12 months
set @STARTDATE = dateadd(year,-1,@STARTDATE);
end else if @DATEFILTER=10 begin
-- All dates
set @STARTDATE = '17530102';
set @ENDDATE = '99991230';
end else if @DATEFILTER=11 begin
-- Current fiscal year
set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=12 begin
-- Previous fiscal year
set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@STARTDATE), 0);
set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year,-1,@ENDDATE), 1);
end else if @DATEFILTER=13 begin
-- Next fiscal year
set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,1,@STARTDATE), 0);
set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year,1,@ENDDATE), 1);
end else if @DATEFILTER=14 begin
-- Within 7 days
set @STARTDATE = dateadd(day,-7,@STARTDATE);
set @ENDDATE = dateadd(day,7,@ENDDATE);
end else if @DATEFILTER=15 begin
-- Within 30 days
set @STARTDATE = dateadd(day,-30,@STARTDATE);
set @ENDDATE = dateadd(day,30,@ENDDATE);
end else if @DATEFILTER=16 begin
-- This calendar year
set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=17 begin
-- Last calendar year
set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=18 begin
-- Next calendar year
set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=19 begin
-- This week
set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=20 begin
-- Last week
set @STARTDATE = dbo.UFN_DATE_LASTWEEK_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTWEEK_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=21 begin
-- Next week
set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=22 begin
-- Next month
set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=23 begin
-- This quarter
set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=24 begin
-- Last quarter
set @STARTDATE = dbo.UFN_DATE_LASTQUARTER_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTQUARTER_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=25 begin
-- Next quarter
set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@STARTDATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=26 begin
--No date assigned
set @STARTDATE = null
set @ENDDATE = null
end else if @DATEFILTER=27 begin
--Remainder of the month
set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ENDDATE,1);
end else if @DATEFILTER=28 begin
--Remainder of this fiscal year
set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@ENDDATE,1);
end else if @DATEFILTER=29 begin
-- Last 6 months
set @STARTDATE = dateadd(month,-6,@STARTDATE);
end else if @DATEFILTER=30 begin
-- Last 24 months
set @STARTDATE = dateadd(year,-2,@STARTDATE);
end
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
return 0;