Month end date
Moderators: chulett, rschirm, roy
Month end date
Hi,
I need to calculate the end date of the month.
i.,e If i give the date like 2007-03-14, it should give me 2007-03-31.
I need to do this in transformer.
DateFromDaysSince(-1, StringToDate(DateToString(RUN_DT, "%yyyy-%mm-%dd"), "%yyyy-%mm-%dd"))
Above code works for previous month end date,
any idea how to calculate given month end date
Thanks
I need to calculate the end date of the month.
i.,e If i give the date like 2007-03-14, it should give me 2007-03-31.
I need to do this in transformer.
DateFromDaysSince(-1, StringToDate(DateToString(RUN_DT, "%yyyy-%mm-%dd"), "%yyyy-%mm-%dd"))
Above code works for previous month end date,
any idea how to calculate given month end date
Thanks
Have you made a search.
Two approach that came to my mind quickly.
Approach 1. You can have 12 if than else condition. Check the Month and assign the predefined last date accordingly.
Approach 2. Fetch month using MonthFromDate() add 1 to it. concatinate 01 as date. Now find the last date of previous month. Take car of 12th month in this case.
Two approach that came to my mind quickly.
Approach 1. You can have 12 if than else condition. Check the Month and assign the predefined last date accordingly.
Approach 2. Fetch month using MonthFromDate() add 1 to it. concatinate 01 as date. Now find the last date of previous month. Take car of 12th month in this case.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Try the following
Code: Select all
StringToDate(svYear:"-":svMonth:"-01", "%yyyy-%mm-%dd")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What happens if MonthfromDate(RUN_DT) is 12? You need an If..Then..Else in svMonth. Then you need to convert the integers to strings to assemble the date-string. And when you do that you need to ensure that the resultant strings have leading zeroes.
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.
Yeah I too have warned OP about Month 12.
If you are using Database, you can simply using the folliwng following logic.
If you are using Database, you can simply using the folliwng following logic.
Code: Select all
trunc(trunc(sysdate,'month')-1)
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
I don' know if you solved this but here is a simple solution:
Declare 2 stage variables as follows:
svYear=If MonthFromDate(RUN_DT) <12 then YearFromDate(RUN_DT) else YearFromDate(RUN_DT) + 1
svMonth=If MonthFromDate(RUN_DT) <12 then MonthFromDate(RUN_DT)+1 else 1
Then in the output derivation for RUN_DT, put:
If MonthFromDate(RUN_DT) = 10 or MonthFromDate(RUN_DT) = 11 then
DateFromDaysSince(-1, StringToDate(svYear : "-" : svMonth : "-01") ) else DateFromDaysSince(-1, StringToDate(svYear : "-" : "0" : svMonth : "-01") )
This will give you what you are looking for.
Declare 2 stage variables as follows:
svYear=If MonthFromDate(RUN_DT) <12 then YearFromDate(RUN_DT) else YearFromDate(RUN_DT) + 1
svMonth=If MonthFromDate(RUN_DT) <12 then MonthFromDate(RUN_DT)+1 else 1
Then in the output derivation for RUN_DT, put:
If MonthFromDate(RUN_DT) = 10 or MonthFromDate(RUN_DT) = 11 then
DateFromDaysSince(-1, StringToDate(svYear : "-" : svMonth : "-01") ) else DateFromDaysSince(-1, StringToDate(svYear : "-" : "0" : svMonth : "-01") )
This will give you what you are looking for.
Re: Month end date
Declare 2 stage variables as follows:
svYear=If MonthFromDate(RUN_DT) <12 then YearFromDate(RUN_DT) else YearFromDate(RUN_DT) + 1
svMonth=If MonthFromDate(RUN_DT) <12 then MonthFromDate(RUN_DT)+1 else 1
Then in the output derivation for RUN_DT, put:
If MonthFromDate(RUN_DT) = 9 or MonthFromDate(RUN_DT) = 10 or MonthFromDate(RUN_DT) = 11 then
DateFromDaysSince(-1, StringToDate(svYear : "-" : svMonth : "-01") ) else DateFromDaysSince(-1, StringToDate(svYear : "-" : "0" : svMonth : "-01") )
svYear=If MonthFromDate(RUN_DT) <12 then YearFromDate(RUN_DT) else YearFromDate(RUN_DT) + 1
svMonth=If MonthFromDate(RUN_DT) <12 then MonthFromDate(RUN_DT)+1 else 1
Then in the output derivation for RUN_DT, put:
If MonthFromDate(RUN_DT) = 9 or MonthFromDate(RUN_DT) = 10 or MonthFromDate(RUN_DT) = 11 then
DateFromDaysSince(-1, StringToDate(svYear : "-" : svMonth : "-01") ) else DateFromDaysSince(-1, StringToDate(svYear : "-" : "0" : svMonth : "-01") )