Date function in transformer stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
harman
Participant
Posts: 8
Joined: Tue Apr 12, 2016 12:51 pm

Date function in transformer stage

Post 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.
SUKH
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post 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.
Last edited by chulett on Mon Jun 19, 2017 2:09 pm, edited 1 time in total.
-craig

"You can never have too many knives" -- Logan Nine Fingers
harman
Participant
Posts: 8
Joined: Tue Apr 12, 2016 12:51 pm

Post by harman »

i tried by using

Code: Select all

DateOffsetByDays(CurrentDate(),1)
but getting
2017-06-20
instead of
2017-05-31
SUKH
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post 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. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
harman
Participant
Posts: 8
Joined: Tue Apr 12, 2016 12:51 pm

Post 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.
SUKH
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post 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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
harman
Participant
Posts: 8
Joined: Tue Apr 12, 2016 12:51 pm

Post 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.
SUKH
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post 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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply