Various Calculations for Date in OBIEE
· Due within Days:
TimestampDiff(SQL_TSI_DAY,CURRENT_DATE,"Time Dimension"."Date")
· Days Past Due:
TimestampDiff(SQL_TSI_DAY, "Time Dimension"."Date",CURRENT_DATE)
· Calculate current year with Current quarter:
EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), CURRENT_DATE, 'YYYY-Q')
· Calculate previous year’s Current Year with previous year’s current quarter:
EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), TIMESTAMPADD( SQL_TSI_YEAR , -1, CURRENT_DATE), 'YYYY-Q')
· Calculate current year with current fiscal quarter:
EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), CURRENT_DATE, 'YYYY')||'-'||CAST("Time Dim"."Fiscal Quarter" AS char)
· Calculate previous year’s current year with previous year’s current fiscal quarter:
EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), TIMESTAMPADD( SQL_TSI_YEAR , -1, CURRENT_DATE), 'YYYY')||'-'||CAST("Time Dim"."Fiscal Quarter" AS char)
· First Day of the CURRENT Month:
TIMESTAMPADD(SQL_TSI_DAY, -1,TIMESTAMPADD(SQL_TSI_MONTH,1,TIMESTAMPADD(SQL_TSI_DAY,1,TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE)*-1, CURRENT_DATE))))
· Last Day of Current Month
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
· First Day of the Previous Month
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
· Last Day of the Previous Month
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
· First Day of the Next Month
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
· Last Day of the Next Month
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
· First Day of the Current Year
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
· Last Day of Current Year
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
· First Day of the Previous Year
TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
· Last Day of Previous Year
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
· First Day of the Next Year
TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
· Last Day of the Next Year
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
· First Day of Current Quarter
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
· Last Day of Current Quarter
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
· Number of days between First Day of Year and Last Day of Current Month
DAYOFYEAR(CURRENT_DATE)
1 comment:
Thank you for the formulas. It saved me some time. I was too lazy to formulate it myself, so rescued me there.
Post a Comment