Date format and date difference
Moderators: chulett, rschirm, roy
Date format and date difference
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
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
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
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
If you understand the logic provided, you can tweak it.
You can use th following to find the last date of the previous month.
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.
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"))
MonthFromDate(). Floor() is also available in PX.
Pls delete the other duplicate post created in Talent forum.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.