Date format and date difference

[srini.dw]

Hi,

1. Iam using DataStage 7.5 PX version, I have date coming as 200703, i need to convert to 2007-02-28 i.e., last day of the previous month, whenever i run the job, can we do it in a single line i.,e Transformer.

I have posted the same question but date was was with different format i.,e 2007-03-02.

2. SELECT FlOOR(MONTHS_BETWEEN
(TO_DATE('2007-05-01','YYYY-MM-DD'),
TO_DATE('2006-01-15','YYYY-MM-DD') )/12) "Months"
FROM DUAL;

I will be having 2 dates(as above) i need to find a difference between 2, only first part iam supposed to take, no decimal part, How iam supposed to do the same in transformer, plz let me know.
(I am suppose to use the same logic as above select statement does)

Plz let me know how to do.

Thank you
Srini

[Ashish]

trim(200703)[1,4]:"-":trim(200703)[5,6] -1:"-":if trim(200703)[5,6] - 1= 1 then 31 else if trim(200703)[5,6] - 1 = 2 then 28 else .....

you can do it same for all the months and give corresponding day please take care for leap years either you can create stage variable or you can straight away give in the above line example if trim(200703)[5,6] - 1 = 2 and trim(200703)[1,4]/4 = 0 then 29 else 28 this is i left on you.

Thanks
Ashish

[kumar_s]

If you understand the logic provided, you can tweak it.

You can use th following to find the last date of the previous month.

Code: Select all

DateFromDaysSince(-1, StringToDate(DateToString(DSLink2.col1:'01', "%yyyy%mm%dd"), "%yyyy-%mm-%dd"))

You can use the following function to find the difference between the months.
MonthFromDate(). Floor() is also available in PX.

Pls delete the other duplicate post created in Talent forum.

[srini.dw]

Hi,

should i used directly
MonthFromDate() - MonthFromDate() or something else

Thanks
Srini

[ray.wurlod]

If the problem has been resolved, please post how it was resolved.

MonthFromDate() - MonthFromDate() is only a partial solution, in that it will only work if the dates are in the same year and the day number of the first date is less than or equal to the day number of the second date. Otherwise the formula needs some adjustment.

Just go through the exercise of constructing some test data to see what I mean. Have dates in the same month, in adjacent months, in non-adjacent months, in different years.

[srini.dw]

Floor(MonthFromDate(DateToString(DSLink2.col1,"%yyyy-%mm-%dd")) -
MonthFromDate(DateToString(DSLink2.col1,"%yyyy-%mm-%dd")))/12

My incoming values are like 2099-12-31 and 2006-12-12.

I have tried the above method, its giving some result like 000000000.

Thanks

[ray.wurlod]

That's what you'd expect, since the month is 12 in each date.

[srini.dw]

How to get the year difference between above 2 dates

Thanks

[ray.wurlod]

Break the dates into their components (for example month_from_date, year_from_date in a Modify stage, or equivalent Transformer stage functions) and apply appropriate logic and arithmetic.