Sep 25 2007
Dynamic Dates Part IV: Yearly Date Ranges
Last Time On This Subject…
This is the fourth in my series about dynamic date objects. The first three included objects for daily, weekly, and then monthly ranges. I am going to complete my collection of Oracle-based dynamic date objects by providing yearly objects in this post.
Current Year
In the monthly post I showed how the Oracle trunc() function can be used to truncate (remove) everything except for the year and month. Here’s how to apply the same technique and get the first day of the year:
trunc(sysdate,'YYYY')
The formula shown above will “round off” the date to the year. Since the return value for this function has to be a date, Oracle returns the year + first month month + first day of the year. Short and simple. I have seen other formulas that convert a date to a string, extract the year with a substring operation, and then combine it back with ‘01′ for the day and ‘01′ for the month and then convert it back to a date. My advice? Use the trunc() function. 
Readers that have followed this complete series will not be surprised with where I go to get the end of the current year. I could get the year from the current date (using string functions as outlined in the prior paragraph) and add in December and 31 for the day. Instead I will use this:
last_day(add_months(trunc(sysdate,'YYYY'), 11))
The advantage of this formula is that everything is based on date math. When I convert a date to character data and then use character functions there is a chance that the result value won’t be a valid date unless I do a lot of error checking. This way is fairly simple.
Why do I apply the trunc() function first? I do that to get to a constant starting point. Get to a known point and the offset (11 months) is always the same.
Last Year
As before I will be setting up objects for the prior year as well. This time instead of using the @Select() function I will do something slightly different. I will give you lots of options.
| Date | Formula |
|---|---|
| Start of Last Year V1 | trunc(trunc(sysdate,’YYYY’)-1, ‘YYYY’) |
| Start of Last Year V2 | add_months(trunc(sysdate,’YYYY’), -12) |
| Start of Last Year V3 | trunc(@Select(’Dynamic Dates\End of Last Year’), ‘YYYY’) |
| End of Last Year | trunc(sysdate,’YYYY’)-1 |
What About Fiscal Calendar Years?
As I said in the prior post on monthly date ranges I would have to use a period calendar table or write custom database functions in order to create fiscal year objects. The standard Oracle database functions work only on a standard calendar. If you have a fiscal calendar and require dynamic yearly 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 four posts I have built daily, weekly, monthly, and now yearly dynamic date objects. All of the prior posts in this series are conveniently linked below in case you missed one. What’s next? I realize that not everyone uses Oracle. Some of these formulas can easily be converted to other databases, and some are more challenging. I will try to show some of these same formulas in other databases. I don’t plan to try to do every date range for every database but will try to cover samples of each.
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.
Related Posts
- Dynamic Dates Part I: Yesterday and Today
- Dynamic Dates Part II: Weekly Date Ranges
- Dynamic Dates Part III: Monthly 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
- trunc( , ‘YYYY) Returns a date value truncated to the first date of the year
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 | add_months(@Select(’Dynamic Dates\End of This Month’) , -1) |
| Start of This Year | trunc(sysdate,’YYYY’) |
| End of This Year | last_day(add_months(trunc(sysdate,’YYYY’),11)) |
| Start of Last Year | trunc(trunc(sysdate,’YYYY’)-1,’YYYY’) |
| End of Last Year | trunc(sysdate,’YYYY’)-1 |
Nice, for some time I have planned on conducting a workshop for consultants at my company on Date functions in Oracle, DB2, and Teradata together with design tips for smart time periods using universe and datamart design techinques.
In researching an answer on BOB I found this link which includes many of the same function results for SQL Server so I decided to post it here:
http://blog.sqlauthority.com/2008/08/29/sql-server-few-useful-datetime-functions-to-find-specific-dates/
Now I don’t have to write my own blog post.
Hope this helps.