Framework Manager - Relative Date Logic Continued

After my last post about relative dates to use in Framework Manager, I received a few requests for additional relative dates and also some questions for clarity. So, today, let's look at a couple of additional relative dates, along with some additional points of consideration.

First Day of Current Week (based on a Sunday through Saturday week) - cast(dateadd({wk}, datediff({wk}, 7, Current_Timestamp), 6) as date)

First Day of Current Month - cast(dateadd({mm}, datediff({mm}, 0, Current_Timestamp), 0) as date)

Last Day of Current Quarter - cast(dateadd({dd}, - 1, dateadd({qq}, datediff({qq}, 0, Current_Timestamp) + 1, 0)) as date)

Yesterday - cast(dateadd({dd}, datediff({dd}, 0, Current_Timestamp) - 1, 0) as date)

Today -  cast(Current_Timestamp as date)

Some additional points of consideration

While implementing these in the framework (thereby making them available as just data items to be pulled into a report) is the most ideal scenario, these functions can be used directly in a data item in a query if you would like. I personally use this relative date logic when producing reports for senior management. They typically want to see reports for last month, last week, YTD, etc. All of this logic allows you to incorporate this.

This logic may cause some problems if you mix it with pure Cognos functions when running large sets of data. It is important to always try to not mix and match functions. Use pure database functions as much as possible to save yourself some headache when running large reports.

All of the relative date functions presented in my last post and this post use a cast(date expression as date) function. If you would like to filter a particular data item using this logic, please also make sure your data item is cast as a date as well. Keeping the data types in sync will ensure proper results from Cognos. Filtering a date-time field with a date filter can produce unexpected results.

Enjoy the relative dates!