Just thought I’d help people out by posting some as a quick reference
Yesterday:
trunc(sydate)-1
Today:
trunc(sysdate)
Start of Current Month:
trunc(sysdate,'mm')
End of Current Month:
last_day(sysdate)
Start of Previous Month:
trunc((trunc(sysdate,'mm')-1),'mm')
End of Previous Month:
trunc(sysdate,'mm')-1
Start of Current Quarter:
trunc(sysdate,'q')
End of Current Quarter:
add_months(trunc(sysdate,'q'),3)-1
Start of Previous Quarter:
trunc(trunc(sysdate,'q')-1,'q')
End of Previous Quarter:
trunc(sysdate,'q')-1
Start of Current Year:
trunc(sysdate,'y')
End of Current Year:
add_months(trunc(sysdate,'y'),12)-1
Start of Previous Year:
trunc(trunc(sysdate,'y')-1,'y')
End of Previous Year:
trunc(sysdate,'y')-1
Please note that this is simply manipulation of the sysdate functionality within Oracle and should in no way detract from the importance of a calendar table. It should assist it.
If the mods want to add this to the database specific sticky, then great.
I once helped someone with a slow running query where the person was using TO_DATE(TO_CHAR(sysdate, ‘MM/DD/YYYY’), ‘MM/DD/YYYY’). The way the SQL was written, the conversion was happening for every record. Replacing the code with TRUNC(sysdate) greatly improved performance.
a) they can cause indexes to be dropped
b) they are executed at every run time, unlike a calendar - straight select.
c) They can cause issues when:- overlaying another BI tool, over your DW, you have to re-write the logic for that tool, or when re-platforming your database - you have to translate the SQL.
The above DB functions are applied only on SYSDATE column, and not on a table.column. So when you use any of the above syntaxes in a condition like
table.date BETWEEN trunc((trunc(sysdate,'mm')-1),'mm') AND trunc(sysdate,'mm')-1
then an index on table.date column will be used.
Even when you have a calendar table that is joined to a fact/transactional table, you can’t avoid situations when pre-defined conditions need to be built in a universe. For instance, a pre-defined condition for “last month data” that will be dynamic and that will use calendar table will look like
calendar_table.date BETWEEN trunc((trunc(sysdate,'mm')-1),'mm') AND trunc(sysdate,'mm')-1
I see nothing wrong about it.
I agree here. But then again, how often do you re-platform systems? Maybe I have been lucky but it has never happened to me in the last 10 years
They are very useful when used for scheduled reports so that you don’t have to worry about prompts.
Sales date between x and y for example - you can simply substitute the objects you want in for x and y (Start of Previous Month and End of Previous Month for example) and you have a report that you can run in, say, November for the whole of October’s sales.
Mak 1 does, though, refer indirectly to something equally important, touched on by Dennis much earlier in the thread. Avoid using dates for joins, use integers. I know there are mixed feelings, but I still stick by using datekey integers rather than surrogate key integers for a calendar table. That is, 14th March 2008 in my calendar table will have a primary key integer as 20080314. It’s indexed, it’s quick but it’s still meaningful.
The last bit (it’s meaningful) gives you two key benefits that I like. Firstly, I can query fact tables when testing reports and data loads without joining on a surrogate key to my calendar table. Secondly, I can streamline my ETL process too, by simply converting the transaction date passed from the source data into an integer version of itself.
The same basic principles apply for all versions of rdbms and are easy to remember.
April 1st this year - date: add_months(trunc(sysdate,‘YYYY’),3)
April 1st last year - date: add_months(trunc(sysdate,‘YYYY’),-9)
Start of financial year - date: (add_months(trunc(sysdate,‘YYYY’),3)+5)
End of financial year - date: (add_months(trunc(sysdate,‘YYYY’),-9)+5)
Rolling last 12 months: @select() BETWEEN trunc(sysdate,‘dd’)-365 AND trunc(sysdate)-(1/(606024))
Dates in the last month: @select() BETWEEN trunc(add_months(sysdate,-1),‘MM’) AND trunc (sysdate,‘MM’)-(1/(606024))
6am previous day - datetime: trunc(sysdate-1)+(time ‘06:00:00’-time ‘00:00:00’)
End previous day - datetime: trunc(sysdate)-1/86400
Date Functions can be very help full, but try to use a table with this information stored into it. A DBA can write statements to update this information automatically.
By doing so you can use this information in your query filters and if needed alter them. This is usefull when you need to run every report again with a predefiend filter set to yesterday (you now only need to update the table and not every report)
i have a report refreshed every 15 minutes. how to get the last 15 minutes?
i try trunc(sysdate, ‘mm’)-15 but it gives -15 months and not -15 minutes.
if we can’t get the last 15 minutes, i think to change the report to be refreshed every one hour. how to get the last one hour?
i try trunc(sydate, ‘hh’)-1 but it gives not exactly one hour. if sysdate is 05/05/2010 11:17, it gives 05/05/2010 11:00 and not 05/05/2010 10:16.
any ideas?
one solution is at report-level (getting the data of today and filter the last 15 minutes or the last one hour) but i prefer a solution at universe-level.
i need to create a Last week filter in universe i tried to create two objects first Start of previous week trunc((trunc(sysdate,‘WW’)-1),‘WW’) and end of previous week as trunc(sysdate,‘WW’)-1 and finally filter Last week As WMUMFRPT.BIZDOC.DOCTIMESTAMP BETWEEN@Select(Transaction\test1) AND @Select(Transaction\test2)
but its not giving the last week data its giving the data between 24th Sep to 29th Sep but it should be 26 Sep to 2nd Oct. please advise how to achieve this.
You will need to add a +2 at the end of both your expressions.
Week functions are often governed by the start day of the week parameter that is set when the database is first installed.
(BOB member since 2003-09-17)
(BOB member since 2007-12-11)
(BOB member since 2003-11-27)
(BOB member since 2005-01-06)
(BOB member since 2006-12-19)
(BOB member since 2005-10-27)