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.
Hey Dave,
I’m working with BO from past 2 yrs. Have worked on 6.5, XIR2, XI3.0 and XI3.1(not extensively), Also on Voyager(pretty much). Have seen a lot of your post and found you really helpful.
I’ve applied a logic to calculate Year to date.
var1=(“1/1″+ formatdate(currentdate();”yyyy”))
var2 = todate(var1;) will give you 1 january current year in date format.
You can then use these variables to calculate YTD.
The similar logic can be used to get the first date of a month. or
=add_months(lastdateofthemonth(currentdate());-x) where x can be any number depending on the month you need.
is there a way to achieve all that at Report Level isntead of Universe Level?
Erika, it’s generally done at the universe because you have to create objects. You can create only variables on a report, and variables can be used for filters but not for query conditions. The universe is really the proper place for this sort of logic.
hi dave can u pls explain the below written in sql
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
‘Last Day of Current Month’
The above is to get last day of current month.
Why is this so difficult in BOE? In an OLAP tool, I just make a date time dimension, tell it the start (for fiscal years …). Then I can use that wherever. But when I want a 13 month average for data on BOE. I have to do tons of gymnastics.
I agree with the above. Why is this so difficult. I’ve never worked anywhere that doesn’t want some date ranges. Cognos and analysis services both hve this functionality – including fiscal years, as drag and drop. BOE seems like its 11 years behind the industry.
Hi Dave,
I have a requirement to show the values for LAST 12 Months including Current Month. I am getting Fiscal Month/Year object from BEX without key in format of “MMM YYYY”. Currently data is available for 3 years and I just want to display the values against this MMM yyyy format including Current Month + Last 12 months.
My report should look like this;
Mar 2014 | 200
Feb 2014 | 123
Jan 2014 | 000
.
.
.
.
.
.
.
.
.
Mar 2013 | 123
I referred many topics on forum but couldn’t succeed.
Kindly help to get it done, I deadly need this.
Thank you in advance.
Getting this Error [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.
When i tried below Code
convert(varchar(8),DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, @Select(Mseg\User Date)), 0)),112)
Please help me how to resolve it..
Thanks
vinay