Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Number of months between Time.Date & Current_Date

Received Response
31
Views
4
Comments
Joe Choueiri-Oracle
Joe Choueiri-Oracle Rank 5 - Community Champion
edited Aug 13, 2024 5:22PM in Oracle Analytics Forum

Hi

Need your help please!

I have a date that comes from the subject area and trying to create a column to show the number of month between that date and the current date, the formula I use is TIMESTANPDIFF(SQL_TSI_MONTH, CURRENT_DATE, "TIME.DATE")  and for some reason it is getting weird results.

As you see in the attached image, the number of month is ZERO for part of Oct and part of Nov and then we see ONE for part of Nov....why Nov is split between the ZERO and ONE?

Thanks

Joe

Answers

  • Joel Acha
    Joel Acha Rank 8 - Analytics & AI Strategist

    Swap the CURRENT_DATE & the “TIME”.”DATE” columns around in your formula.

  • Joe Choueiri-Oracle
    Joe Choueiri-Oracle Rank 5 - Community Champion

    Thanks Joel but if I do that..the only thing that change is the number in the column in the left where the formula is but we will still see Nov as -1 and as 0- see attached image.

    Thanks again

    JoeUntitled2.png

  • Joel Acha
    Joel Acha Rank 8 - Analytics & AI Strategist

    Can you check your query logs and share the SQL that has been generated by the BI Server?

  • Gianni Ceresa
    Gianni Ceresa Mod

    Isn't it just "normal" ?

    When you posted CURRENT_DATE = 05 November

    You get a 1 from the 21 November.

    21 - 05 = 16 days, just a bit more than half a month.

    If the round 0.5000001 to the integer you get 1 from the 21 November.