Sequence Job End of Month Date

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Sequence Job End of Month Date

Post by jackson.eyton »

Hi everyone,
I'm hoping to use the UserVariables functions to calculate the end of month date for the current month. I found a routine by Ray that does some similar things that looks like it could be adapted to work. I'd rather not go through all that if it's something that's pretty simple to do in the sequence.

I can get the current date from: Oconv(Date(),"D-YMD[4,2,2]")

not quite sure how to build the date from date parts, something like: (Oconv(Date(),"D-Y[4]") : Oconv(Date(),"D-M[2]") : Oconv("01") + 1 month) - 1 day)
-Me
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Never mind, I got it!
Oconv(Iconv(@YEAR:@MONTH+1:"01","D-YMD[4,2,2]")-1, "D-YMD[4,2,2]")

Took a little help from here, found with google but not the internal search of the forum :?:

viewtopic.php?p=297245
-Me
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Did you do an exact search? For whatever reason, it's the only one that isn't date limited.
-craig

"You can never have too many knives" -- Logan Nine Fingers
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

That is the default search setting for me so it would appear that I did.
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Check out the supplied Transform called MONTH.LAST (and emulate it). It uses

Code: Select all

ConvertMonth(@YEAR:":":@MONTH,"L")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Ray, that's interesting... It seems that MONTH.LAST/ConvertMonth() requires the external date string format, and the @DATE/@YEAR/@MONTH variables appear to be in internal format. Thus the only way I could get this to work was as follows:
Oconv(MONTH.LAST(Oconv(@DATE, "D-YM[4,2]")),"D-YMD[4,2,2]")

Granted it DOES work and it is a little clearer as to what exactly its doing, that's a plus!

Considering this however, why does the following work?
Oconv(Iconv(@YEAR:@MONTH+1:"01","D-YMD[4,2,2]")-1, "D-YMD[4,2,2]")

@YEAR gives me the return of '18', @MONTH = '10', which does happen to be correct to the current month.... Ok, confirmed.... @YEAR for 2018 = '18' whereas @MONTH and @DAY are the literal two digit number of the month and day.
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

What happens to your expression in December?
@MONTH is 12
(@MONTH + 1) is 13
Iconv() fails.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Ray that is a great point, one that I have accounted for in a parallel job that does the same thing using a transform stage. I just overlooked that here. To be fair I AM using the MONTH.LAST now instead of what I had originally written. It's definitely more clear in the job what it's doing so we're sticking with that.

My curiosity now is more in regards to Iconv and it's interactions with internal/external data formatting. It appears that it can use a combination of internal values and literal strings to convert the entire thing to one single internal representation?
Iconv(@YEAR:@MONTH:"01","D-YMD[4,2,2]")

The above for example will give me the first day of the month in internal representation. What's interesting is that @YEAR is already an internal value, but @MONTH is not, and clearly the day concatenated at the end is just a hard coded string. Not really something that has to be answered here as it'd be off topic to this post, I just found it interesting.

Thanks a ton for pointing me toward those x.LAST functions I've altered our jobs to use them! :D
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

None of @YEAR, @MONTH or @DAY is in internal format. They are the numeric external format representations.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply