Month end date

[srini.dw]

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

[kumar_s]

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.

[srini.dw]

YearfromDate(RUN_DT):"-":svMonth:"-":"01" (DataType is Date)
where
svMonth is Month + 1 (DataType is Integer)
RUN_DT is 2007-01-01
Output column is giving is *******.

Thanks

[kumar_s]

Use StringToDate() conversion, if its within Datastage. And use TO_DATE() if it need to be loaded into Database.

[srini.dw]

Iam doing this in transformer

StringToDate(svYear:"-":svMonth:"-":"01") (datatype date)

svYear is YearfromDate(RUN_DT) (datatype integer)
svMonth is MonthfromDate(RUN_DT) + 1 (datatype integer)
But the above one is still showing as *******

Thanks

[DSguru2B]

Make sure you have 2 digit month. I explained this in your other post.

[kcbland]

Add one month to your date, change the day to 01, subject one day from that result.

[srini.dw]

StringToDate(svYear : "-" : svMonth : "-":"01")

Month 2 digit, Year 4 digit

It still giving me ******

Thanks

[DSguru2B]

Try the following

Code: Select all

StringToDate(svYear:"-":svMonth:"-01", "%yyyy-%mm-%dd")

[ray.wurlod]

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.

[kumar_s]

Yeah I too have warned OP about Month 12.

If you are using Database, you can simply using the folliwng following logic.

Code: Select all

trunc(trunc(sysdate,'month')-1)

[splayer]

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.

[kavuri]

Implemented the above logic and it works fine for all months expect for this 2007-09-01 month.
Do i have to change my logic...

[kumar_s]

Nothing special about this date! What doest work?
What is the output that you get? And what is that you expected?

[dattan]

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") )