Sep 12 2007

Dynamic Dates Part III: Monthly Date Ranges

Categories: Dynamic Dates,Universe Design Dave Rathbun @ 2:57 pm

Last Time On This Subject…

In the first post in this series I introduced the idea of dynamic date objects. The intent was to show how a universe designer can create dynamic date objects that can be used to schedule reports with a condition that changes over time. The first post covered the basic concepts and included “Today” and “Yesterday” dynamic date objects. The next post covered weekly ranges. In this post I will further extend the concept to monthly ranges. As with the other posts so far in this series I will be using Oracle functions.

Working In the Current Month

Getting the first date of the current month is really simple with Oracle. There are a number of different ways but here is what I think is the simplest method:

trunc(sysdate,'MM')

I mentioned before that trunc() is an overloaded function. So far in this series I have used it only to remove the time portion of a date/time value. By adding an additional argument I can remove more than the time element from the date. The formula shown above will “round off” the date to the month and year. Since the return value for this function has to be a date, and every date must have a day value, Oracle returns the year + month + the first day of the month. How convenient. 🙂

The end of the current month is harder, if only because months have a different number of days. Personally, I use the knuckle trick to remember which months have 30 versus 31 days. Since Oracle doesn’t have hands I need a different approach. I could use a case statement to check which month I am in and determine the number of days in the month using something like this:

case to_char(sysdate,'MON')
when 'JAN' then 31
when 'FEB' then 28
when 'MAR' then 31
when ...
when 'DEC' then 31
end

That is a lot of code to write, and it doesn’t handle leap years. As it turns out there is a much easier way to approach this. All I need to do is recognize what the last day of every month has in common. It doesn’t matter if the day number is 28, 29, 30, or 31, there is always one constant: The day after the last day of one month is always the first day of the following month.

I can use the trunc() function to get the first day of this month. How can I get the first day of next month? I could try adding 30 days and then truncating, but that would still require me to know how many days are in each month in order to check the boundary conditions. It should not be that hard, and it turns out that I can get to the last day of a month using a new function called add_months().

Finding the End of a Month

Here is the syntax for the add_months() function:

ADD_MONTHS(<date>, <number_of_months>)

The number_of_months can be either a positive or negative integer. This allows me to move either forwards or backwards for as many months at a time as are needed. What can I do with this? Suppose that I get the first day of this month, add one month to that date, and then subtract one day? Where do I end up? I end up on the last day of this month. I take something constant (the first day of a month) and use it to derive something that is dynamic (the last day of the month) with a little creative math. 😀

So here is some code for an object named Last Day of Current Month that uses the add_months() function and the logic outlined in the prior paragraph:

add_months(trunc(sysdate,'MM'), 1) -1

This formula works for every month of the year, and every possible scenario of leap year as well. It will handle moving across a year boundary (December – January). It will handle the fact that some months have different numbers of days… for example, if I add one month to January 31 I will get February 28 (or 29 as appropriate). It will not add 31 days since January has 31 days. Here is a table showing some output from an Oracle query that shows what happens when I cross a month boundary using this function:

Input Date First Day of Month Last Day of Month +1 Month -1 Month
28-AUG-07 01-AUG-07 31-AUG-07 28-SEP-07 28-JUL-07
29-AUG-07 01-AUG-07 31-AUG-07 29-SEP-07 29-JUL-07
30-AUG-07 01-AUG-07 31-AUG-07 30-SEP-07 30-JUL-07
31-AUG-07 01-AUG-07 31-AUG-07 30-SEP-07 31-JUL-07
01-SEP-07 01-SEP-07 30-SEP-07 01-OCT-07 01-AUG-07
02-SEP-07 01-SEP-07 30-SEP-07 02-OCT-07 02-AUG-07
03-SEP-07 01-SEP-07 30-SEP-07 03-OCT-07 03-AUG-07
04-SEP-07 01-SEP-07 30-SEP-07 04-OCT-07 04-AUG-07

August has 31 days. September has 30. Notice what happens on August 31 when I add one month to the date? I get September 30, which is what I want to see. What Oracle does is provide a mapping of “last days” so that you get the expected results without having to do a lot of specific date checking. Adding a negative month also works as expected, which becomes important when I try to create objects for last month.

It’s All About the Creative Process

If you read and understood everything in the prior section, that’s great. Now throw it all away. 😆

The formula that I showed using add_months() works. It dates back to my early days as an Oracle developer. The need to find the last day of a month is so important that several versions back (I think in version 8? but I am not sure…) Oracle gave us a function to do exactly that. The function name, oddly enough, is last_day() and here is the syntax:

LAST_DAY(<date>)

Short, simple, and to the point. As I was writing this post I thought about skipping the add_months() example. I went ahead and included it to show the “creative process” that I try to apply to find an alternate path when no immediate solution presents itself.

And for Last Month?

I don’t want to stop after handling the current month. I want to be able to provide dynamic dates for the starting and ending dates for last month too. As I did in the weekly post I will use the @Select() function to reference the complex expressions that I have created so far and then subtract one month as shown here. To get the end of last month no matter how many days there are in “this” month I will apply the last_day() function again.

add_months(@Select('Dynamic Dates\Start of This Month'), -1)
last_day(add_months(@Select('Dynamic Dates\Start of This Month'), -1))

That’s all there is to it. 8)

What About Fiscal Calendar Periods?

In order to provide dynamic dates for a fiscal calendar I would have to use a period calendar table or write custom database functions. The standard Oracle database functions work only on a standard calendar. If you have a fiscal calendar and require dynamic monthly dates I strongly suggest that you look at creating a calendar table that supports your requirements. Note: I won’t be covering that process in this series of posts.

Summary

Over the past three posts I have built daily, weekly, and monthly dynamic date objects. I did not cover how to use these objects in a query in this post but I did in the first post, which is conveniently linked below. Remember that these objects are presented will not parse in Designer. They don’t reference a table and therefore will generate an error. As long as these objects are used with at least one “real” object on a query they will function perfectly fine.

I still have a few more posts to go before I wrap up this series. The next post will be fairly simple as it will include standard objects for working with the current year and prior year date ranges. You won’t have to wait until next year for that one to come out. 8)

Related Posts

Oracle functions used in this post

  • add_months() Returns an offset in months ahead or behind from the date given
  • last_day() Returns the last day of the month for the date given as an argument
  • to_char() Converts a date into a string value with a wide variety of formats
  • trunc() Returns a date value with the time truncated off
  • trunc( , ‘MM’) Returns a date value truncated to the first of the month

Dynamic Dates With Oracle Functions

Date Formula
Today trunc(sysdate)
Yesterday trunc(sysdate-1)
Start of This Week next_day(trunc(sysdate-7), ‘Sunday’)
End of This Week next_day(case to_char(sysdate,’Day’) when ‘Saturday’ then trunc(sysdate-1) else trunc(sysdate) end, ‘Saturday’)
Start of Last Week @Select(‘Dynamic Dates\Start of This Week’) – 7
End of Last Week @Select(‘Dynamic Dates\End of This Week’) – 7
Start of This Month trunc(sysdate,’MM’)
End of This Month last_day(trunc(sysdate))
Start of Last Month add_months(@Select(‘Dynamic Dates\Start of This Month’) , -1)
End of Last Month last_day(add_months(@Select(‘Dynamic Dates\Start of This Month’), -1))

21 Responses to “Dynamic Dates Part III: Monthly Date Ranges”

  1. Comment by Rakesh

    Hi Dave,

    This site is amazing. I have been going through your site for couple of days now.

    My Environment is Business Objects 6.5 SP4 and i am using Oracle. I want to create an object at universe level and My question is how do i get data for the whole curent month and i am also looking for previous month also.I am not looking for condition.

    Thanks In Advance

    Rakesh

  2. Comment by yadavendra singh

    Hi dave

    I am new to ur site well to tell u frankly iam visting ur site first time i think it almost solve all my problems. its great to have a sit of this

    yadavendra singh

  3. Comment by yadavendra singh

    Hi dave

    I have an requirement of this kind . I need to pass a value to the prompt dynamically such that. I have 2 different prompts well in the second prompt iam using the forst prompt values by having decode in that. now my requirement is if an user select the forst prompt the secong prompt should take that value. for ex this is my prompt

    tablename.colname > =
    last_day(
    to_date(
    trim(
    decode(
    @Prompt(’02.Effective month From’,’A’,’Date’,’mono’,constrained),’Latest Month’,
    (select to_char(viewname.colname,’Month yyyy’)
    from view where LATEST_MONTH_VW.FEED_NAME=’xyz’ AND
    viewnmae.colname IN @Prompt(’01.ddd’,’A’,’classname\objname’,’MONO’,Constrained) ),
    @Prompt(’02.ddd’,’A’,’classname\objname’,’mono’,constrained)
    )
    )
    ,’Month yyyy’)
    )

    now my first prompt is
    @Prompt(’01.ddd’,’A’,’classname\objname’,’MONO’,Constrained)

    now iam using this first prompt in second prompt if user select first prompt then that value i used in second prompt or if user wont select that the i shoul pass some default value dynamically like ‘ALL'(select ‘ALL’ from dual) now my problem is when iam creating an webi report even thogh iam select second prompt it is forcing me to enter some value in the (@Prompt(’01.ddd’,’A’,’classname\objname’,’MONO’,Constrained) i cant just leave it because i need to enter some value in it.so how to control this please help me out.

    env: boxir2 ,oracle10g

    yadavendra singh

  4. Comment by Dave Rathbun

    Hi, what you have discovered is that prompts do not interact with each other. Unfortunately that’s the way it works right now. There does not appear to be a way to have the results of one prompt affect a second prompt. Anytime the @prompt() syntax appears, there will be a prompt displayed.

  5. Comment by kumar

    Hi Dave,

    Thanks for the wonderful article. Could you also please take up a case for defining Hourly Ranges based on Dynamic Dates.

    Vishal

  6. Comment by Dave Rathbun

    Hi Vishal, thanks for your comment. I’m afraid that I don’t quite understand your request. By definition any day has 24 hours, and since the prompt is for a date it’s at the wrong granularity to return a range of hours.

  7. Comment by Bill

    Hi Dave,

    This site is amazing.. here I have a requirement that I need to find previous 3 months using a month and Year prompt.

    ADD_MONTHS only takes date is there any way that I can do this?

    Thanks,
    Bill

  8. Comment by Dave Rathbun

    Hi, Bill, what you will need to do is convert your year+month into a date. For example, if you have 2009-09 as the year month, you can convert it to a date using the following:

    to_date('2009-09' || '-01', 'YYYY-MM-DD')

    This concatenates the day number ‘-01’ to the end of the year+month string, then converts that value to a date using the to_date() function with an appropriate format string. If your year+month is in a different format you may use slightly different syntax but the concept is the same.

  9. Comment by Bill

    Hi Dave,

    Thank you for your reply. This works for me. But my scenario is:
    I am using like this:

    Code:
    datecol between add_months(to_date(to_char(’01’||’-‘||”||’-‘||”)),-3)and to_Date(to_char(’01’||’-‘||”||’-‘||”))

    If user selects current month then display as of current date if user selects previous month then display as of last day of that month.

    Is that be incorporated?

    Thanks,
    Bill

  10. Comment by Bill

    HI dave,

    got this… I am using lastday(to_Date(to_char(’01?||’-‘||||’-‘||))) this resolved the issue.

    Thanks,
    Bill

  11. Comment by Dave Rathbun

    Bill, I don’t think your code got posted properly. You might need to read the “tips for formatting comments” notes to get your complete code posted.

  12. Comment by kay

    Good Stuff Mr. Rathbun…saved me a lot of trouble.

  13. Comment by Anjali

    Hi,
    I need to add 91 days to the date entered by the user.How can i do it?
    I need : Expected Begin Date BETWEEN “Date entered by the User” AND (“Date entered by the User” +91) “.

  14. Comment by Dave Rathbun

    Hi Anjali, your question doesn’t really fit this post. And to be honest, it seems to me you have already answered it yourself. 🙂 If you’re using an Oracle database you can simply create a predefined condition that prompts the user for a date and does the math as required, something like:

    table.some_date between @prompt('Enter Date','D',,,) and @prompt('Enter Date','D',,,)+91

    For other databases the structure might be different. For example SQL Server offers the dateadd() function.

  15. Comment by Aruna

    Dave, I need to create report and automate it (every monday morning) and select the last business day available. I have Posting_date object in the report. How to select last business day with this..
    Let me know

  16. Comment by Dave Rathbun

    Hi, Aruna, I see that you have posted the same question on BOB. I have added a reply to that post.

  17. Comment by Khan

    can someone publish the Dynamic Dates With db2 Functions. Thanks

  18. Comment by sapbobj567

    Hi Dave,When i select date in webi Eg : Date = 10/15/2013,Then report should display data for that month(1st oct 2013 to 31st Oct 2013) and for that week (13th Oct to 19th Oct 2013) ,I only have date object in universe and database is Netezza

  19. Comment by Jennifer

    Hi Dave – we have tons of filters in universe designer that pulls back a rolling 13 months but we have not been successful in recreating those filters in IDT. We have SQL back end; and this is an example of a filter we have in universe designer: tblInvoice_Header_History.BillingDate >= DATEADD(m,-14,getdate()) Can you help with what it should be in IDT?

  20. Comment by Flower

    Hallo Dave,
    do you have also solution for dynamic date in Information Design Tool, database is ist “Sybase IQ”.

  21. Comment by Rena Winton

    Can the dynamic dates be used as report filters, element links? I’m using Webi 4.1.