Number of months between Time.Date & Current_Date

Question

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

Gianni Ceresa · Answer

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.

Joel Acha · Answer

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

Joe Choueiri-Oracle · Answer

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

Joe