Aug 22 2007

Dynamic Dates Part I: Yesterday and Today

Categories: Dynamic Dates, Universe Design Dave Rathbun @ 11:05 am

Hitting a Moving Target

Does anybody really know what time it is?
Does anybody really care?
If so I can’t imagine why…

With apologies to the rock group Chicago, but I could not help starting out this post with a quote from one of their most famous hit records. Yeah, I have records. Deal with it. :-P

I am going to start out this post with a very obvious statement. There are two ways to get reports: Interactive and Scheduled. Interactive reports can have prompts that allow me to specify which values I want to see, and many times those prompts will include dates or date ranges. Since they are interactive I can change the dates each time I run the report.

But what about scheduled reports? I would hardly want to schedule a report that always ran for August 22, 2007, right? I want that date parameter value to change. Maybe I want the report to always run for “yesterday” or “last week” or “the second Tuesday of the month” or anything along those lines. The challenge is, of course, that the value for “yesterday” changes each day I run the report. How do I hit that moving target?

It’s All About Today

The first component that I need to solve this issue is knowing what “today” means for any given database. Every system that I have worked with has provided at least one mechanism to get today’s date:

Database Function or Token
DB2 current date
Oracle sysdate
Informix TODAY
SQL Server getdate()
Sybase getdate()
Teradata DATE

Some databases use a function and others provide a token or pseudo-column instead. I am going to use Oracle syntax for the rest of this post but the general concepts and techniques that I plan to show are often able to be ported to other database systems.

Using sysdate

As mentioned I can get today’s date from Oracle using the pseudo-column sysdate. (It’s called a pseudo-column because you can get it from any table in the database.) Once I know what today is I can derive other interesting values. The first step is often to get only the date (instead of a full date/time result) and the Oracle function trunc() takes care of that. So today can be obtained by:


The trunc() function in Oracle is an overloaded function which means I can use it with a variety of different syntaxes. (It may be that I could create a more portable version of the same objects using the cast() function.) Now that I have today, what can I do with it? I mentioned earlier in this post that one of the common requests is to be able to run a report for “yesterday” on a scheduled basis. Yesterday is the day before today on most calendars. :-) So I can create an object for “yesterday” using either of the following:


Both of these will return the exact same result, so it doesn’t matter which one I pick. I am going to use the first one for reasons that will be detailed in a future blog post.

Conditions in a Scheduled Document

Once these objects are created I have a couple of options to consider. First, I can make predefined conditions that are based on a specific date object like Order Date or Shipped Date. I will often do this for frequently used conditions. But an important option that should not be overlooked is creating a simple object called Yesterday with the code posted above. After this object is created then I can create any date condition, using any date object in the universe, by using the “Select an Object” query operand. This option is available in full client from version 5.x forward (and possibly in version 4.x too, I don’t remember). It is also available in Web Intelligence XI and might be in 6.5.

I will create Today and Yesterday objects using Oracle as my target database. Here are the objects:

Today Object Definition

Yesterday Object Definition

And here is the result I get when I parse either of these two objects:

Parse Results

This is typical! Most if not all of my objects built for this strategy will fail to parse. One of my current universes has several hundred objects that fail to parse during a universe integrity check, and that is just fine. I don’t want to reference any table with these objects (not even the special Oracle “dual” table) because then I would be limited as to how I can use them. If I use the dual table and don’t include any joins then I would get Cartesian warnings. Objects like this are perfectly safe to use as long as they participate in a query with at least one object that does use a real table. And since these objects would never be used by themselves that is okay.

Now that I have my objects built, here’s how I will use them. I am going to build a condition that will return all orders entered yesterday by using the following steps.

  1. Create a condition on Order Date Equal to and select Object from the condition operand options.

    Building the query filter

  2. Browse the universe to my Dynamic Dates for Scheduling class and select Yesterday.

    Select the object

  3. Confirm the selection and review the condition.

    Order date equal yesterday

That generates a condition that looks just perfect for my purposes. The order date will be compared to a dynamic “yesterday” object. Since that object is based on the system date then that object will change in value on each new daily execution of the scheduled report. If order dates are date/time values then to get the full range I can use a between operator instead. For this I need both of my dynamic date objects, as shown next. This condition will return everything from midnight yesterday to midnight today.

Order date between yesterday and today

What does the SQL code look like?
ORD.ORDERDATE BETWEEN trunc(sysdate-1) AND trunc(sysdate)

The condition added to the where clause by this condition is bold to try to make it stand out more. I can see that there is no specific date in the query, therefore as the report instance is executed on its daily scheduled basis I will get what I expect: a daily report with orders from yesterday.

Predefined Conditions

The biggest advantage to creating the general date objects is that they can be used to compare against any other date object in the universe. Of course I can also consider creating predefined condition objects in my universe. These objects are often built for conditions that are too complex for users to create, or there is a need for consistency across all reports, or for convenience. But in this case the number of predefined condition objects could become a challenge.

I would have to create a cross product of date objects, dynamic date elements, and desired date operators. That’s a lot of predefined conditions in my universe! :shock: So far I have two dates (order date and ship date) and two dynamic elements (today and yesterday). I could have:

  1. Order Date Equal To Today
  2. Order Date Equal To Yesterday
  3. Order Date Between Yesterday and Today
  4. Order Date … ?

As you can see it could get out of hand. And all of the conditions above can be created by the user with the proper query techniques if I just build the dynamic objects. In theory you could build a huge set of predefined conditions. In practice I generally will build one or two that are used frequently and let the user do the rest. So I will create objects for orders placed yesterday and for orders shipped yesterday and leave it at that until I get requests for something different.

Orders entered yesterday

Orders shipped yesterday


This is one of the standard elements of any universe that I design. This post is designed to introduce the concepts. The next post in this series will cover some more Oracle-specific date functions and how they can be used to do very creative things with dynamic date objects for scheduling.

Oracle functions used in this post

  • trunc() As used in this article it returns a date value with the time truncated off

52 Responses to “Dynamic Dates Part I: Yesterday and Today”

  1. Comment by DOTJake

    Just a note about DB2. I had a problem doing this with CURRENT_DATE and the querys were running for a long time. I had to ‘wrap’ my statement with the COALESCE in order for the database to not have to do the calculation for each row (I think that’s what my DBA said).

    I have done many of these dynamic date objects. I’ve created begin and end date objects for previous and current week, month, fiscal year, calendar yesr, etc. and then used these (using ‘between’) for many conditions.

    Heres a couple of examples.

    Prev Month Begin Date:

    Prev Week End Date:

    Prior Fiscal Year Begin Date (our FY is July-June):

  2. Comment by DOTJake

    Last example cut off. Here is the full exapmle for Proir Fiscal Yeatr Begin Date (FY is July-June)


  3. Comment by Dave Rathbun

    Hi, DOTJake, and thanks for sharing. I have finally figured out that when you post code that includes < and > you need to wrap the comment text in “code” formatting. That will keep your characters from being interpreted as web links. I have edited your first example, so you can see the code is in courier (fixed) font which also seems to me to make it easier to read.

    If you can try submitting your example again, but put <code> in front of the code, and </code> at the end, I think it will come out.

    Something like this:
    <code>If x < y then 1 else 0</code>

  4. Comment by Anita Craig

    Nice, Dave. Now you need to put a pointer from the Reporter and Designer FAQs over to this. ;-)

    You’ve accidentally left Sybase out of your table — it uses getdate() also.

  5. Comment by Dave Rathbun

    I didn’t “accidentally” leave it out, I left it out on purpose. :)

    Many years ago I worked with a client that was using Sybase IQ. It did not have any internal functions, not even getdate(). That was the one and only time I ever used @Script() in a universe, and it was to get the current date. Since then I have not worked with Sybase of any flavor at a client site in probably eight or nine years, and I don’t see it gaining any market share over the next few years either. But point taken, and I will add it in to the table. Thanks, and welcome to my blog. 8)

  6. Comment by DOTJake

    Now that I know how to post code, here is the Proir Fiscal Year Begin Date (FY is July-June) example:


  7. Comment by DOTJake

    It seems to be cutting off after the ‘greater than or equal to’ symbol

  8. Comment by kumar

    Thanks Dave for your lesson.I learnt a new concept now.
    Let me extend a little bit on this
    What if the user wants this to be dyamanic like nstead of yesterday it may be 30 days or 20 days back
    There is no fixed value he may enter any value…

  9. Comment by Dave Rathbun

    Testing comment…
    Case when something >= something else

    Hm. Now I don’t know what to tell you. Here is the code for what I just posted:

    <code>Case when something >= something else</code>
  10. Comment by Dave Rathbun

    kumar wrote:

    What if the user wants this to be dyamanic like nstead of yesterday it may be 30 days or 20 days back
    There is no fixed value he may enter any value…

    Kumar, if you look, you have answered your own question. :) Thing about the part I have emphasized… how would you do that? With a prompt, correct?

    So instead of an object called Yesterday I will create an object called Prompt Days Ago that looks like this:

    trunc(sysdate - @prompt('Enter days ago','N',,,))

    This will prompt the user for the number of days. If the user enters 0, they get today. If they enter 1, they get yesterday. If they enter 7, they get a week ago. And so on from there.

  11. Comment by Kaivalya

    Hey Dave!
    You have been fabulous again…i mean as usual. I have been learning lots of tips & tricks from you in the past…and it is going on!

    Thanks a lot for such a great contribution for BOBJ comunity.

  12. Comment by kumar

    Yes Dave I got you.Thanks

  13. Comment by Dave Rathbun

    @Kaivalya, you are welcome, thank you for taking the time to let me know my contributions are helpful.

    @Kumar, happy to help. It was a good question. 8)

  14. Comment by Sunitha Kubsad

    Hey Dave,

    We had a similar requirement in out last project where in we had to schedule the reports every week for current week data and also to give the end user an option to enter Start date and End date when refreshing the report.

    To resolve this we did the following:
    1) Create two objects “StartDate Prompt” and “StopDate Prompt “(not filters) with the following definitions respectively:
    StartDate Prompt: @Prompt (’Enter Fiscal Begin Date:’,'D’,, mono,free)
    StopDate Prompt: @Prompt(’Enter Fiscal End Date:’,'D’,,mono,free)

    2) Create “DateRange Prompt” filter where in Case statement is used to assign default values if the user does not enter any values for the prompts.
    @Prompt(’Enter Fiscal Begin Date:’,'D’,,mono,free)=’ ‘
    Then (current date – 7 days)
    else (@Select(DATE RANGE\ StartDate Prompt) ) end AND case when
    @Prompt(’Enter Fiscal End Date:’,'D’,,mono,free)=’ ‘
    then (current date – 1 days)
    else ( @Select(DATE RANGE\ StopDate Prompt) ) end

    While scheduling, if single space is given as the parameter then the report can be scheduled weekly to get current week’s data.

  15. Comment by raj

    The date prompt is very helpful. But I also wanted to give the option to select the time i.e. 12/12/07 4:30:00pm to 12/12/07 6:30:00pm. Can you please help me with that.


  16. Comment by Indu

    Hi Dave,

    Thats a wonderful tip which i learnt today.
    Thank you so much.

    Dave :

    ….. you Rock the world .. with your creativity in using …..Business Objects !!

    Merry Christmas and a Happy New Year.

    Thank you.

    With sincere regards

  17. Comment by Dave Rathbun

    Raj, the prompts that I have built here all assume that the date is to be truncated, that is, to be selected without a time. If you are using Oracle then you can simply add the hours/minutes factor back to the resulting date.

    For example, today was built as trunc(sysdate). If you wanted to run that from 4:30PM to 6:30PM then you can add the appropriate number of hours, as in:


    4:30PM is 16:30 in 24 hour time, and 16:30 is 16.5 hours, and so 16.5 / 24 gives you the decimal portion of a day that matches 4:30PM. You would, of course, have to use a “between” operator for all of your conditions since you have a start time and an end time.

    Hope this helps get you started.

  18. Comment by Mike Nagatoshi

    These are great tips! I’m going to go off on a relatped tangent. Is it feasible to create a dynamic schedule for a report. I want to trigger a report based on a table used as a change log. If there’s a new record, I want a report to go out. Is this API-only functionality?

  19. Comment by Dave Rathbun

    Hi, Mike, sorry for the delay. I just got back from a vacation at a site run by your employer. ;-)

    What version of Business Objects are you using? What I have done in the past using older versions is set up a failure rule on the schedule. If there are no new records then the report fails and thus is not distributed. If there are new records, then the report is distributed. I have not played with XI enough to know how to suggest doing the same thing in that version yet.

  20. Comment by Ron Durbin

    Is there any reason why the Yesterday and Today objects will work with a BETWEEN statement and not an EQUAL TO in my Universe? The Birthdate and other objects are a Date. SQL code looks like this in BO:
    This works:Birthdate BETWEEN trunc(sysdate-1) AND trunc(sysdate)
    This returns no data: Birthdate = trunc(sysdate-1)

  21. Comment by Dave Rathbun

    The most likely case is that Birthdate is not truncated. Therefore when you compare with a strict test like “equal to” it must match exactly. The botton line is that there is no reason why “Today” or “Yesterday” objects would not work using equality checks.

  22. Comment by Ron Durbin

    Dave, thanks for the reply. Great website for BO! Birthdate was not a good example. I apologizae for that. Admission date and time is a better one. If I change the WHERE statement in the SQL to trunc(pat_ENT_PATIENT.ADMIT_DT) = trunc(sysdate-1) and choose not to regenerate it works. That means either the end users do this or I create explicit objects for each date time field such as Admission Date/Time, Admission Date and Admission time using trunc. Any better ideas? Sorry, just took over this universe and I am in learning mode. The site has been very helpful!

  23. Comment by Dave Rathbun

    If your date is not truncated just use a between operation rather than equal to. For example, if you say Admission Date Between Yesterday and Today, and “Yesterday” and “Today” are defined as I have done them in this post, then you’ll get everything from yesterday at midnight up to today at midnight. If you have an admission date that is exactly midnight, it could get counted twice. In which case you can do Admission Date >= Yesterday and Admission Date < Today. That lets you use the standard objects that you might build without having to edit your SQL.

    Editing SQL is a bad idea. :)

  24. Comment by Ron Durbin

    Dave, Thank you so much!!! Editing the SQL is exactly what I want the end user to avoid! You’ve been extremely helpful as well as your site! Thanks again!

  25. Comment by Raj

    Dave, you are wonderful, it was really helpful… Thanks!!!

  26. Comment by Tbaby

    Pls can you kindly help me to figure this out;
    I have succeeded in creating my previuos and current month object, but i want their corresponding revenue to be display on a separate column and the measure object not aggregated. How do i go about it.

    Note; If create a report with both objects(previous & current month), i want to display each of the data on a separate column.
    I await your prompt response.

  27. Comment by Dave Rathbun

    Tbaby, welcome and thank you for your comment. Your question isn’t solved by creating dynamic date objects, it’s going to be solved by using a calendar table and multiple query passes. In other words, a completely different approach. :) I am in the process of writing up one way to do this for a presentation at the user conference next month (October) in Dallas. It’s fairly complex, and not something I am able to answer here in the limited space, and not on this post since it’s a completely different technique.

    Come back in a month and you should be able to download the presentation from my conferences page.

  28. Comment by David

    How do you deal with this in business days including holidays. This is critical in financial data?

  29. Comment by Dave Rathbun

    Hi, David, and welcome. If you have any non-standard issues to deal with I much prefer to use a calendar table than try to work with database functions. It makes the solution much cleaner that way.

  30. Comment by David Hampton

    Hey Dave, I have read over the blogs entries above, and also review the BOB website. I can not seem to figure out how to use the dayofweek function when trying to create a predefined condition.

    In short, I am trying to identify which day it is, and then subtract x number of days from a transaction date data object within my universe.

    Here is the code I have to date.
    Case when dayofweek(CURRENT_DATE)=2 then
    @Select(Transaction Details\Transaction Date) = (current_date – 3 days)
    when dayofweek(CURRENT_DATE)=3 then
    @Select(Transaction Details\Transaction Date) = (current_date – 1 days)

    I get the following error when I try to parse my condition.

    [IBM][CLI Driver][DB2] SQL0104N An unexpected token “=” was found following “”. Expected tokens may include: “END”. SQLSTATE=42601

    I appreciate any help.


  31. Comment by David Hampton


    I finally was able to make so progress on this.

    @Select(Transaction Details\Transaction Date) = COALESCE(
    (case when dayofweek(CURRENT_DATE) = 2 then (CURRENT DATE – 3 days)
    when dayofweek(CURRENT_DATE)=3 then (CURRENT DATE – 2 days)
    ELSE (CURRENT DATE – 1 days)

    But I would like to add to the when dayofweek(CURRENT_DATE)=3 then (CURRENT DATE – 2 days) part.. if current_date =3 I actually need to pull Current date – 2 and current date – 1.

    Any ideas?

    Thanks again,

  32. Comment by CA_BO

    Hi Dave,
    Thanks for your tips.Its really helpful to novices like me in BO.

    My users need to get report in email, which will tell them from which DB/env the report data are coming from either in the subject line/mail body or report name.Can this be possible through scheduling report feature in XI R2?Dynamically appending the DB/Env name while running and fetching data?

  33. Comment by Dave Rathbun

    Hi, CA, welcome. Please note that your question really has nothing to do with this blog post. As per my support policy I would suggest that you post your question on BOB instead. Thanks.

  34. Comment by Mohanraj

    Thanks for the awesome ideas !

  35. Comment by Glen BRown

    Hello, I realize this is an older post but when I try and add the Today/Yesterday objects is posts the error you mention above and never creates the objects. What am I missing. (Bo XI r3.2)

  36. Comment by Deepthi

    Hello Dave, I am not able use a range of values/ Interval prompt for 0FISCPER time dimension.
    But if i give from date and to date same value report executes sucessfully, but if i give a range of date then report does not give any data though there s data for the particular values.
    Can you please help me?

  37. Comment by Chakri

    Hi Dave, Could you say a bit more about the statement (”What I have done in the past using older versions is set up a failure rule on the schedule”) in your comment #19. How & Where did you setup failure rule on schedule?

  38. Comment by Dave Rathbun

    In the old days of Broadcast Agent (BCA) you could define a rule for a job. For example, you could check the row count or a sum (total) of a measure for a specific value. If the check failed, then the report failed even if it returned data and it was not distributed.

  39. Comment by Umesh

    I tried the Magic Date with simplest implementation but somehow the “GETDATE()” and “@prompt date compare” do not work. I did a work around to make it work in OPTION 2 but that is not what you guys were able to implement. Is there some setting to change to make TSQL command evaluate and trigger. I am using SQL Server 2008 and BO XIR4. Appreciate your hekp..

    OPTION 1 – does not work
    Case When @Prompt(’Enter date’,'A’,'01/01/1900′,mono,free) =’01/01/1900′
    — This does not work- always returns 1/1/1900 instead of current date
    then @Select(Common Dates\Current Date)
    –– GETDATE() will not work, always returns 1/1/1900
    @Prompt(’Enter date’,'A’,,mono,free)

    OPTION 2 – Works
    CASE WHEN @Prompt(’Enter date or Today’,'A’,,mono,free,not_persistent)=’Today’ — alpha compares and works
    THEN cast(convert(char(10),getdate(),23) as datetime) – WORKED
    — GETDATE() returns 1/1/1900
    @Prompt(’Enter date or Today’,'A’,,mono,free)

  40. Comment by Umesh Pradhan

    I need to schedule report using magic date lastdayofcurrentyear. This works till end of the year. Some reports need to run after 31 dec for the previous year for a week. How do I change the magic date?

    Example..I have to create a monthly report with date condition for the year. It is cumulative and runs for the year.

    Report is run for daterange where datefrom is fristdayofCurrentYear and dateto is max of lastdayofPrevmonth and lastdayofCurrentYear
    Feb 1/1/yy to 1/31/yy,
    Mar 1/1/yy to 2/28/yy
    The last run in Jan 1/1/yy to 12/31/yy.. Rundate being in jan, current year fails.

    Any help will be appreciated.

  41. Comment by Dave Rathbun

    Well, the entire concept of the magic date is that it’s driven by the current system date. :) You could get around that by adding a case statement that checks to see if the date is in the range of January 1 to January 5 (or whatever works for you) and substitute December 31 instead. That would give you a several day window to continue to run the “last day of current year” as if it were in the previous year.

  42. Comment by akash

    Hi Dave,

    I have a requirement for To and From Data Prompt. I am facing some challenge with Date prompt. Example whenever user select Date from 1st Jan 2013 to 31st JAN 2013 then it takes from 1st JAN 2013 12:00:00 AM to 31st JAN 2013 12:00 AM. So it excludes the Data for 31st JAN 2013. I tried using DateAdd function but it didnt not working for me (possibly i am doing something wrong). Below is code generated by report when you edit it after running. Seems it is taking date as string. Please suggest.
    VW_PROJECT_TASKS.TASK_COMPLETED_DATE BETWEEN ‘01-01-2012 00:00:00′ AND ‘29-02-2012 00:00:00′

  43. Comment by Dave Rathbun

    Usually in this case you truncate the date so the values only have a date value and no time element.

  44. Comment by Shirley Ray

    Hi Dave,

    I have a similar requirement but in my requirement, I need all the Sales Orders of previous day when user opens the webi but user should have ability to change and view even prior to yesterday’s Sales Orders? How can I do that? Is it possible?


  45. Comment by Dave Rathbun

    Hi, Shirley, there is at least one blog post here about a “magic date” solution that can help. Instead of a dynamic date that changes based on the system date, it allows you to either use a place-holder date that resolves to “today” or “yesterday” but can also be overridden by inputting a new prompt value.

  46. Comment by Shirley

    Hi Dave,

    My datasource is Teradata and I am creating universe in IDT. When I tried to implement your approach but It did not work. while creating the pseudo object in the select box when I wrote Current_date which is equivalent to sysdate of oracle in Teradata, it gave me an error “The query does not reference any table when attempting to build the WHERE clause. (WIS 00022)” Any suggestions?

  47. Comment by Dave Rathbun

    That’s fairly standard when you use what is called a “pseudo column” in your object definition. The CURRENT_DATE does not come from any particular table, so the FROM clause is empty. You can ignore this error.

  48. Comment by George

    Hi Dave,

    Very interesting post. I am trying to do something similar in a Universe (IDT) where the dataset extracted depends on the users selection of a Parameter at run time which are 1, 2, 3, 4 with descriptions Last Full Month, Month To Date, Last Full Year and Year To Date. If this is possible, how do I go about it ? Any suggestions.

  49. Comment by Riya

    Hi Dave,

    Is it possible to set default sysdate prompt in WebI directly without creating object in Universe. And also the WebI should use this date as optional prompt not as mandatory prompt. If I try to customize in custom sql , then it is showing like mandatory prompt. Please provide your suggestions.


  50. Comment by Dave Rathbun

    Hi, Riya, the trick here is that special logic is required behind the scenes to translate something static into something dynamic (like a standard token into the current system date). It has been a while since I tried, but I don’t remember any tricks in Web Intelligence that would allow me to do something like that directly in the query panel.

  51. Comment by anup

    Hi Dave,

    In a single BO report how can we populate first column with previous day data and second column with current day data?
    As explained, above in your post is it the same?


  52. Comment by Dave Rathbun

    Anup, I don’t think your question really fits this post. The post is about generating dynamic dates so you don’t have to hard code a condition for “yesterday” but can instead use the system-generated date value.

    To split data into two columns, I would either use an “If” statement or perhaps consider a crosstab block with the date at the top of the column.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:


Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.