Date Conversion issues

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

ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Consider using a BASIC Transformer stage. Date conversion functions in DataStage BASIC are very flexible. All you would then need would be:

Code: Select all

Oconv(Iconv(inLink.TheColumn, "D"), "D-YMD[4,2,2]")
The Iconv() function with just "D" as its argument will convert eight recognisable date formats into DataStage internal format, and the Oconv() will convert that into YYYY-MM-DD format.

You will always be in strife if they're sending you mm/dd/yyyy and dd/mm/yyyy in the same column.
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 »

Alright, premium membership just purchased, I'll do some research on Oconv/Iconv and doing this in a server job instead of a parallel. I should soon be able to read your whole post. Thanks Ray!
-Me
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Ok, I have created a server job and attempted to do some date conversions with that. For now the job is failing due to too many warning messages. The warning messages are all similar to the following:
DateConversionBASIC..XFM: At row 23617, link "OUT", while processing column "TLTMD2"
Value treated as NULL
Attempt to convert String value "2282-01-18" to Date type unsuccessful
That column only has the following unique values:
  • TLTMD2
    0
    2282018
    2272018
    20180228
    20180301
and I am using the following BASIC Transform derivation for that column:
Oconv(Iconv(IN.TLTMD2, "D"), "D-YMD[4,2,2]")

do I need to pad the mddyyy format with a 0 and then right( ,8) for it to read it correctly? That is what I have to do with a parallel transform....
-Me
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

That code Ray posted was for getting your "date" in as a string and then back out in another string converted to a standard format but it sounds like perhaps your derivation is targeting an actual date field? Without me digging back through all of this, what data type are you expecting as the output of this?
-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 »

Thanks Chulett, the input data is coming in a numeric column containing non standardized dates. We're wanting to convert these to standardized dates, the biggest issue is the dates can be in any number of formats:
mddyy
mmddyy
mddyyyy
mmddyyyy
yyyymmdd
yyyyddd

I have to account for any one of those and convert it. There are some things that can help isolate a limited subset of formats that might be in the column, for example some of the columns are Numeric(6), some Numeric(7), and others Numeric(8). The output column type is indeed DATE

*UPDATE
I edited the derivation to use just ICONV, however now I see that ICONV is designed to turn a string into an internal format. I will try again converting the incoming value to string, and then internal format.
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Check out this article particularly the page on date conversion with Iconv()
Last edited by ray.wurlod on Sun Mar 04, 2018 6:41 pm, edited 1 time in total.
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 »

I did see that yesterday, giving it another look now, one note for anyone else that reads this, the links on the page ray listed do not work for me as-is, though they merely need a www. prefix.
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Links corrected. Thanks for the catch. :D
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 »

I will have to shelf this for a bit. I cannot seem to get the server job with Iconv to work with the data that I have. I'm not 100% sure why. I did make some progress, I see now using one static date string "yyyy-mm-dd" format I was able to get that to convert, at least according to the lack of warning logs for that column. I've added some IF conditions to handle the actual empty strings and set to @NULL. I do get warnings still that cause the job to fail, and it appears as if the ICONV is not converting the string to an internal date. which leads me to warnings like this:

Code: Select all

DateConversionBASIC..XFM: At row 59250, link "OUT", while processing column "TLTMD2"
Value treated as NULL
Attempt to convert String value "" to Date type unsuccessful
While using a derivation like this:

Code: Select all

IF IN.TLTMD2 <> 0 THEN Iconv(Char(IN.TLTMD2), "D") ELSE @NULL
Where the column only has these distinct values:
  • TLTMD2
    3022018
    0
    3012018
    20180302
    20180305
    20180301
You can clearly see two differing formats, and whats interesting is that in my testing of using a static string formatted as "yyyy-mm-dd" the the "D" argument alone for ICONV would not convert it as an automatically recognized format. I did attempt to use a derivation as follows, but it appears the transform stage did not like the Status() function at all:

Code: Select all

IF Status(Iconv(Char(IN.TLTMD2), "D")) = 0 THEN  Iconv(Char(IN.TLTMD2), "D") ELSE IF Status(Iconv(Char(IN.TLTMD2), "DYMD")) = 0 THEN Iconv(Char(IN.TLTMD2), "DYMD") ELSE @NULL
At this point, do we want to move this post to the server jobs forum? I was able to simply add to my parallel job derivation to account for another date format and can merely suppress the warning logs on those for now. I am getting 100% conversion with parallel transform derivations on all currently known valid date formats the end users whimsically decide to use. I would like to keep this open if I could to learn the nuances of OCONV/ICONV in this scenario as it does seem like a slightly more straight forward method and hopefully one that performs better in the end.
-Me
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

That's not how the Status() function works, alas. If you want to include that functionality, you'll need to build it into a routine.
The Char() function doesn't do what you're expecting either.

Try this:

Code: Select all

If IN.TLTMD2 Matches "7N" Then Iconv(IN.TLTMD2,"DYJ") Else If IN.TLTMD2 Matches "8N" Then Iconv(IN.TLTMD2,"DYMD") Else @NULL 
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