Page 1 of 1
Date function in transformer stage
Posted: Mon Jun 19, 2017 1:07 pm
by harman
Hello...
i have function LAST_DAY(CURRENT DATE-1 MONTH) returning results 2017-05-31 in sql giving last date of month.i needs to use the same in transformer stage .
i tried by using
DateFromDaysSince(-30, CurrentDate()) function but not works.
Please help me on this.
Posted: Mon Jun 19, 2017 1:17 pm
by chulett
You might want to check out the DateOffsetByDays function. If you take the system date and "offset" it by the ((current day + 1) * -1) days, you'll get the last day of the previous month.
Posted: Mon Jun 19, 2017 1:55 pm
by harman
i tried by using
but getting
2017-06-20
instead of
2017-05-31
Posted: Mon Jun 19, 2017 2:08 pm
by chulett
Of course, because you simply added 1 day to the current date. Re-read what I wrote... after I fix a very important omission about flipping the sign.
Posted: Mon Jun 19, 2017 2:59 pm
by harman
Thanks Chullet for reply.
i tried as you suggested
DateOffsetByDays(CurrentDate(),((currentDate() + 1)*-1)) but somehow job is not getting compiled might have some issue on function.
Can you please check this again.
Posted: Mon Jun 19, 2017 3:04 pm
by chulett
Sorry but that isn't quite what I suggested. You need to offset it by the current day (plus 1 and made negative) not the current date.
Posted: Mon Jun 19, 2017 5:54 pm
by harman
Sorry chullet in transformer stage under Date and Time function i saw there is only currentdate not current day so i tried by using
Code: Select all
DateOffsetByDays(CurrentDate(),((DaysInMonth(CurrentDate()) + 1)*-1))
but getting 2017-05-19.
Can you please check again.
Sorry i am not getting on the offset can you please help what should i give the whole function in transformer stage.
Posted: Mon Jun 19, 2017 7:13 pm
by chulett
Read what the DaysInMonth() function actually returns, it's not what is needed here. While you probably could cobble together a solution using it, I was suggesting a function like MonthDayFromDate() instead.