Trouble viewing images? Click on images to enlarge.

 

VDM has built in Reserved Words that you can use for Date Filters inside of FSGS. This article will give you descriptions and usage examples of each Reserved Word.

 

Special Date Filtering Options/Reserved Words

 

 

Single Date Reserved Words - Explanations and Examples

This section contains Reserved Words that will return a single date value.

 

@Date - Ran on 10/28/2020

dbo.Orders.OrderDate = @Date dbo.Orders.OrderDate = '2020-10-28'

Returns the current date

 

 

@FirstDayThisMonth - If ran on 10/28/2020

dbo.Orders.OrderDate = @FirstDayThisMonth dbo.Orders.OrderDate = '2020-10-01'

Returns the first day of the current month based on the date the report is executed

 

 

@LastDayofLastMonth - If ran on 10/28/2020

dbo.Orders.OrderDate = @LastDayofLastMonth dbo.Orders.OrderDate = '2020-09-30'

Returns the last day of the previous month based on the date the report is executed

 

 

@LastDayThisMonth - If ran on 10/28/2020

dbo.Orders.OrderDate = @LastDayThisMonth dbo.Orders.OrderDate = '2020-10-31'

Returns the last day of the current month based on the date the report is executed

 

 

@LastDayXMonthsAgo - If ran on 10/28/2020

Gets the last day of X months ago; @LastDay6MonthsAgo

dbo.Orders.OrderDate = @LastDay2MonthsAgo dbo.Orders.OrderDate = '2020-08-31'

Returns the last day of X months ago.

 

 

@LastDayXMonthsOut - If ran on 10/28/2020

Gets the last day of X months in the future; @LastDay6MonthsOut

dbo.Orders.OrderDate = @LastDay2MonthsOut dbo.Orders.OrderDate = '2020-12-31'

Returns the last day of X months forward from when the View was ran.

 

 

@TodayXYearsAgo - If ran on 10/28/2020

Replace X with the number of years in the past; @Today1YearsAgo, @Today2YearsAgo

dbo.Orders.OrderDate = @Today1YearsAgo dbo.Orders.OrderDate = '2019-10-28'

Returns the current date X years ago from when the View was ran.

 

 

@XDaysAgo - If ran on 10/28/2020

Replace X with a number of days in the past; @30DaysAgo, @94DaysAgo

dbo.Orders.OrderDate = @5DaysAgo dbo.Orders.OrderDate = '2020-10-23'

Returns the date X days ago from when the View was ran.

 

@XDaysOut - If ran on 10/28/2020

Replace X with a number of days in the future; @30DaysOut, @94DaysOut

dbo.Orders.OrderDate = @5DaysOut dbo.Orders.OrderDate = '2020-11-02'

Returns a date that is X Days forward from when the View was ran.

 

 

@XDayYMonthsAgo - If ran on 10/28/2020

Replace X with the Day of the month and Y with the number of Months in the past; @5Day2MonthsAgo

dbo.Orders.OrderDate = @5Day2MonthsAgo dbo.Orders.OrderDate = '2020-08-05'

Returns the X Day of Y Months ago from when the View was ran.

 

 

@XDayYMonthsOut - If ran on 10/28/2020

Replace X with the Day of the month and Y with the number of Months in the future; @5Day2MonthsOut

dbo.Orders.OrderDate = @5Day2MonthsOut dbo.Orders.OrderDate = '2020-12-05'

Returns the X Day, Y Months forward from when the View was ran.

 

 

@XQuarterYYearsAgoStartDate - If ran on 10/28/2020 

Replace X with the quarter and Y with the number of years ago; @1Quarter1YearsAgoStartDate

dbo.Orders.OrderDate >= @1Quarter1YearsAgoStartDate dbo.Orders.OrderDate >= '2019-01-01'

Returns the first day of X Quarter, Y years ago from when the View was ran.

 

 

@XQuarterYYearsAgoEndDate - If ran on 10/28/2020 

Replace X with the quarter and Y with the number of years ago; @1Quarter1YearsAgoEndDate

dbo.Orders.OrderDate <= @1Quarter1YearsAgoEndDate dbo.Orders.OrderDate <= '2019-03-31'

Returns the last day of X Quarter, Y years ago from when the View was ran.

 

 

@XQuarterYYearsOutStartDate - If ran on 10/28/2020 

Replace X with the quarter and Y with the number of years forward; @1Quarter1YearsOutStartDate

dbo.Orders.OrderDate >= @1Quarter1YearsAgoStartDate dbo.Orders.OrderDate >= '2019-01-01'

Returns the first day of X Quarter, Y years forward from when the View was ran.

 

 

@XQuarterYYearsOutEndDate - If ran on 10/28/2020

Replace X with the quarter and Y with the number of years forward; @1Quarter1YearsOutEndDate

dbo.Orders.OrderDate <= @1Quarter1YearsOutEndDate dbo.Orders.OrderDate <= '2019-03-31'

Returns the last day of X Quarter, Y years forward from when the View was ran.

 

 

@XMonthsAgoStartDate - If ran on 10/28/2020

Gets the first day of X months ago; @1MonthsAgoStartDate

dbo.Orders.OrderDate <= @1MonthsAgoStartDate dbo.Orders.OrderDate <= '2020-09-01'

Returns the first day of X months ago from when the View was ran.

 

 

@XMonthsAgoEndDate - If ran on 10/28/2020

Gets the last day of X months ago; @1MonthsAgoEndDate

dbo.Orders.OrderDate <= @1MonthsAgoEndDate dbo.Orders.OrderDate <= '2020-09-01'

Returns the last day of X months ago from when the View was ran.

 

 

@XMonthsOutStartDate - If ran on 10/28/2020

Gets the first day of X months forward; @1MonthsOutStartDate

dbo.Orders.OrderDate <= @1MonthsOutStartDate dbo.Orders.OrderDate <= '2020-09-01'

Returns the first day of X months forward from when the View was ran.

 

 

@XMonthsOutEndDate - If ran on 10/28/2020

Gets the last day of X months forward; @1MonthsOutEndDate

dbo.Orders.OrderDate <= @1MonthsOutEndDate dbo.Orders.OrderDate <= '2020-11-30'

Returns the last day of X months forward from when the View was ran.

 

 

@XWeeksAgoStartDate - If ran on 10/28/2020

Gets the first day (Sunday) of X weeks ago; @1WeeksAgoStartDate

dbo.Orders.OrderDate <= @1WeeksAgoStartDate dbo.Orders.OrderDate <= '2020-10-18'

Returns Sunday of X weeks ago from when the View was ran.

 

 

@XWeeksAgoEndDate - If ran on 10/28/2020

Gets the last day (Saturday) of X weeks ago; @1WeeksAgoEndDate

dbo.Orders.OrderDate <= @1WeeksAgoEndDate dbo.Orders.OrderDate <= '2020-10-24'

Returns Saturday of X weeks ago from when the View was ran.

 

 

@XWeeksOutStartDate - If ran on 10/28/2020

Gets the first day (Sunday) of X weeks forward; @1WeeksOutStartDate

dbo.Orders.OrderDate <= @1WeeksOutStartDate dbo.Orders.OrderDate <= '2020-11-1'

Returns Sunday of X weeks forward from when the View was ran.

 

 

@XWeeksOutEndDate - If ran on 10/28/2020

Gets the last day (Saturday) of X weeks forward; @1WeeksOutEndDate

dbo.Orders.OrderDate <= @1WeeksOutEndDate dbo.Orders.OrderDate <= '2020-11-07'

Returns Saturday of X weeks forward from when the View was ran.

 

 

@XYearsAgoStartDate - If ran on 10/28/2020

Gets the first day of the year X years ago; @1YearsAgoStartDate

dbo.Orders.OrderDate <= @1YearsAgoStartDate dbo.Orders.OrderDate <= '2019-01-01'

Returns the first day of the year X years ago from when the View was ran.

 

 

@XYearsAgoEndDate - If ran on 10/28/2020

Gets the last day of the year X years ago; @1YearsAgoEndDate

dbo.Orders.OrderDate <= @1YearsAgoEndDate dbo.Orders.OrderDate <= '2019-12-31'

Returns the last day of the year X years ago from when the View was ran.

 

 

@XYearsOutStartDate - If ran on 10/28/2020

Gets the first day of the year X years ago; @1YearsOutStartDate

dbo.Orders.OrderDate <= @1YearsOutStartDate dbo.Orders.OrderDate <= '2021-01-01'

Returns the first day of the year X years forward from when the View was ran.

 

 

@XYearsOutEndDate - If ran on 10/28/2020

Gets the last day of the year X years ago; @1YearsOutEndDate

dbo.Orders.OrderDate <= @1YearsOutEndDate dbo.Orders.OrderDate <= '2021-12-31'

Returns the last day of the year X years forward from when the View was ran.

 

 

Date Range Reserved Words - Explanations and Examples

 

This section contains Reserved Words that will return a date range instead of a single date value.

 

@ThisMonth - If ran on 10/28/2020

dbo.Orders.OrderDate = @ThisMonth dbo.Orders.OrderDate >= '2020-10-01' AND dbo.Orders.OrderDate <= '2020-10-31'

Returns everything from the first day to the last day of the current month.

 

 

@LastMonth - If ran on 10/28/2020

dbo.Orders.OrderDate = @LastMonth dbo.Orders.OrderDate >= '2020-09-01' AND dbo.Orders.OrderDate <= '2020-09-30'

Returns everything from the first day to the last day of the previous month.

 

 

@ThisYear - If ran on 10/28/2020

dbo.Orders.OrderDate = @ThisYear dbo.Orders.OrderDate >= '1/1/2020' AND dbo.Orders.OrderDate <= '12/31/2020'

Returns everything from the first day of the year to last day of the year.

 

 

@XWeeksAgo - If ran on 10/28/2020

Replace X with a number of weeks in the past; @4WeeksAgo, @8WeeksAgo

dbo.Orders.OrderDate = @2WeeksAgo dbo.Orders.OrderDate >= '2020-10-11' AND dbo.Orders.OrderDate <= '2020-10-17'

Returns everything from the first day X weeks ago to the last day of that week. (Sunday - Saturday ) X Weeks Ago

 

 

@XWeeksOut - If ran on 10/28/2020

Replace X with a number of weeks in the future; @4WeeksOut, @8WeeksOut

dbo.Orders.OrderDate = @2WeeksOut dbo.Orders.OrderDate >= '2020-11-08' AND dbo.Orders.OrderDate <= '2020-11-14'

Returns everything from the first day X weeks forward to the last day of that week. (Sunday - Saturday ) X Weeks Forward

 

 

@XMonthsAgo - If ran on 10/28/2020

Replace X with a number of months in the past; @6MonthsAgo, @10MonthsAgo

dbo.Orders.OrderDate = @5MonthsAgo dbo.Orders.OrderDate >= '2020-05-01' AND dbo.Orders.OrderDate <= '2020-05-31'

Returns a date range that is the entire month X months ago.

 

 

@XMonthsOut - If ran on 10/28/2020

Replace X with a number of months in the future; @6MonthsOut, @10MonthsOut

dbo.Orders.OrderDate = @2MonthsOut dbo.Orders.OrderDate >= '2020-12-01' AND dbo.Orders.OrderDate <= '2020-12-31'

Returns a date range that is the entire month X forward.

 

 

@XYearsAgo - If ran on 10/28/2020

Replace X with a number of years in the past; @3YearsAgo, @4YearsAgo

dbo.Orders.OrderDate = @2YearsAgo dbo.Orders.OrderDate >= '2018-01-01' AND dbo.Orders.OrderDate <= '2018-12-31'

Returns a date range that is the entire year X years ago.

 

 

@XYearsOut - If ran on 10/28/2020

Replace X with a number of years in the future; @3YearsOut, @4YearsOut

dbo.Orders.OrderDate = @2YearsOut dbo.Orders.OrderDate >= '2022-01-01' AND dbo.Orders.OrderDate <= '2022-12-31'

Returns a date range that is the entire year X years forward.

 

 

@XQuarterYYearsAgo- If ran on 10/28/2020

Replace X with the quarter and Y with the number of years in the past; @3Quarter2YearsAgo

dbo.Orders.OrderDate = @3Quarter2YearsAgo dbo.Orders.OrderDate >= '7/1/2018' AND dbo.Orders.OrderDate <= '9/30/2018'

Returns a date range for the X Quarter, Y Years in the past from when the View was ran.

 

 

@XQuarterYYearsOut- If ran on 10/28/2020

Replace X with the quarter and Y with the number of years in the future; @3Quarter2YearsOut

dbo.Orders.OrderDate = @3Quarter2YearsOut dbo.Orders.OrderDate >= '7/1/2022' AND dbo.Orders.OrderDate <= '9/30/2022'

Returns a date range for the X Quarter, Y Years forward from when the View was ran.