Date calculations inside DS

[DLam]

Good Morning DS Guru's,

I am in search of more wisdom. I need to figure out how to do date calculations in Transformer. The Business Requirements are as follows:

Take the due date and count the number of months until the prod date.

So I need the difference in dates represented as number of months. The values CAN be fractions/decimals like 1.23 months.

I tried using the DaysSinceFromDate and converting it to months (take the number of days and multiplying it by 12/365). This works for the majority of my data but not all of the time because not every month contains the same number of days.

I've thought about using my date dimension but I want to see if DS had anything that can do as I want.

Your help is always appreciated.

[ArndW]

You need to refine your rules. Let us say you want to calculate from the 15th of one month to the 15th of next. What is the result if month 1 has 28 days and month 2 has 31, or if both have 30? The simplest method is to take the delta in days; if that really cannot be done then you would need to compute the number of partial days for each month to make a real numbered number-of-months.

[hiral.chauhan]

You need to use a date dimension that has a "month_number" that starts at 1 for the first month on your date dimension and goes like this :

Lets say your date dimension starts at January 01, 1980.

Code: Select all

Date           Month_Number
1980-01-01	      1
    ....         ....
1980-12-01	     12
1981-01-01	     13
    ....         ....
1981-12-01	     24
1982-01-01	     25
    ....         ....

So on and so forth. Then you can subtract your number of months the way you can use DateDiff in SQL or MonthsBetween in Oracle.

Hope that helps.

-Hiral