Dave's Adventures in Business Intelligence

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

• Dynamic Dates Part I: Yesterday and Today
• Dynamic Dates Part II: Weekly Date Ranges
• All posts in this series

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))