Special Date Filtering Options

Cory Fifield -

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

mceclip0.png

 

 

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.

 

 

@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.

 

 

 

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.

 

 

@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.

 

 

Have more questions? Submit a request

Comments

  • Avatar
    Jeremy Mapes

    Is there any way to parse the date for a number or character field. For example... I have a stats table (Cache) that has a Stat Month and a Stat Year. So I need to take last month's date and parse it... I can use the Cache commands it seems but I need to know how the @LastMonth is laid out if I'm going to try to parse the date to fill the Stat Month (character field) and State Year (number field). Thanks!

  • Avatar
    Tony Coffman

    Hello Jeremy, to leverage the Special Date Filtering options you would need to apply the filter against a date field. You can create a date field using Stat Month and Year by creating a custom database field/function in VDM using the following example:
    {fn CONVERT(STRING(SQLUser.Clnt_Stats_MTD.FOUR_DIGIT_YEAR,'-',SQLUser.Clnt_Stats_MTD.STAT_MONTH,'-','01'),SQL_DATE)}

    Please reference ticket 13188 for more information.