Using an SQL Date Formula in OBIEE Report

3371114

Summary

How do I display previous 30 days of data

Content

Could someone please show me how to tweak the below formula.  I’d like to pull data from the previous 30 days; however, it seems to be pulling for the future 30 days instead. 

               timestampadd(sql_tsi_day, 30, current_date)

When I change the formula to the below example it gives me an error and doesn't return any data:

                timestampadd(sql_tsi_day, -(30), current_date)

Thank you.

Version

OBIEE 11.1.1.9.0

Christian Berg-0racle

timestampadd(sql_tsi_day, -30, current_date)

No brackets

RichardChan

or if you want the previous month timestampadd(sql_tsi_month,-1,current_date) if you want to take into account the variable number of days in a month

3371114

Thank you Christian for your help.  I was trying to use the formula in a Filter and had already written it the way you'd recommend before I asked for help but was still unable to get it to work.  However, I located an old report I used once before with a similar formula and it worked in a Filter.  Thanks again for your support.

"Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

3371114

Thank you Richard for your help.  I was trying to use the formula in a Filter and had even written it the way you'd recommend before I asked for help but was still unable to get it to work.  I located an old report I used once before with a similar formula and got it to work in a Filter.  Thanks again for your support.

"Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Saresh-Oracle

Hi,

Does this link helps:

https://obiee4conceptby.wordpress.com/2012/05/24/dynamic-filter-by-date/

Thanks,

Sareesh

3371114

Saresh,

Thank you for this information.  I actually had tried the formula timestampadd(sql_tsi_month,-30,current_date) in the filter portion of my analysis but could not get it to work so I used the below formula instead and got it to work.  Thanks again for the site information, I'll keep it handy for future use.

"Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Wendy Ware

Sounds like you have a working solution but, for future reference, be careful because timestampadd(sql_tsi_month,-30,current_date) will subtract 30 months (not 30 days or 1 month as you are intending.)

3371114

Thank you Wendy for catching that.  What I meant to say was that I'd tried "timestampadd(sql_tsi_month,-1,current_date)" in the filter portion of my analysis previously but could not get it to work, so I had to use the below formula instead which is working.

"Submission Dates"."Hire Start Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))