DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
harman
Participant



Joined: 12 Apr 2016
Posts: 8

Points: 62

Post Posted: Mon Jun 19, 2017 1:07 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41909
Location: Denver, CO
Points: 215066

Post Posted: Mon Jun 19, 2017 1:17 pm Reply with quote    Back to top    

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.

_________________
-craig

"I was a king once, then they made me a bird. Then Mother Church came along and turned us all into saints, trolls and fairies. General Mills did the rest." - Mad Sweeny

Last edited by chulett on Mon Jun 19, 2017 2:09 pm; edited 1 time in total
Rate this response:  
Not yet rated
harman
Participant



Joined: 12 Apr 2016
Posts: 8

Points: 62

Post Posted: Mon Jun 19, 2017 1:55 pm Reply with quote    Back to top    

i tried by using
Code:
DateOffsetByDays(CurrentDate(),1)
but getting
Quote:
2017-06-20
instead of
Quote:
2017-05-31

_________________
SUKH
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41909
Location: Denver, CO
Points: 215066

Post Posted: Mon Jun 19, 2017 2:08 pm Reply with quote    Back to top    

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

"I was a king once, then they made me a bird. Then Mother Church came along and turned us all into saints, trolls and fairies. General Mills did the rest." - Mad Sweeny
Rate this response:  
Not yet rated
harman
Participant



Joined: 12 Apr 2016
Posts: 8

Points: 62

Post Posted: Mon Jun 19, 2017 2:59 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41909
Location: Denver, CO
Points: 215066

Post Posted: Mon Jun 19, 2017 3:04 pm Reply with quote    Back to top    

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

"I was a king once, then they made me a bird. Then Mother Church came along and turned us all into saints, trolls and fairies. General Mills did the rest." - Mad Sweeny
Rate this response:  
Not yet rated
harman
Participant



Joined: 12 Apr 2016
Posts: 8

Points: 62

Post Posted: Mon Jun 19, 2017 5:54 pm Reply with quote    Back to top    

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:
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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41909
Location: Denver, CO
Points: 215066

Post Posted: Mon Jun 19, 2017 7:13 pm Reply with quote    Back to top    

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

"I was a king once, then they made me a bird. Then Mother Church came along and turned us all into saints, trolls and fairies. General Mills did the rest." - Mad Sweeny
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours