DSXchange

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)

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

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

That is the default search setting for me so it would appear that I did.

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

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.

What happens to your expression in December?
@MONTH is 12
(@MONTH + 1) is 13
Iconv() fails.

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

None of @YEAR, @MONTH or @DAY is in internal format. They are the numeric external format representations.