DSXchange

Greetings,

I have an incoming mainframe date which thus far I have converted from a decimal to a string, which comes in the format CYYMMDD ('C' for century, 1 or 0) and I need to convert it to DD-MMM-YY:00:00:00. Any ideas how I can do this in a derivation? I figured I'd start by trimming off the leading 'C' digit, but then...?

TIA,

ff

So... do you actually need to convert it to a timestamp or to a string, an external representation of the timestamp's value using the format mask you posted?

1. Convert to date (relying on implicit DecimalToString if you wish).
2. Extract the date components with appropriate functions and format strings.
3. Assemble the required output string.

Thanks Guys,

The outbound column should be a timestamp. Thus far I have converted the incoming decimal to a string, will now execute steps 2 and 3.

For a timestamp column, you don't need "steps 2 and 3", they are all about a string as the target. You need to look at the StringToTimestamp function.

Franklin trots out his Cobol soapbox...

Question the data. Look at the format and ask: was this particular design choice actually necessary?

A single digit designating century is far from standard. The first lesson learned out of the Y2K fiasco is that the days of restricted or expensive storage are past, 8-bit is obsolete, and Cobol dinosaurs (being one, ahem) should not be permitted to continue coding as if those things were still true.

Internal requirements should not be divulged in an open forum. One is left to speculate: the values of the "C" are ambiguous. Is it a flag, with perhaps "1" indicating "20"? Is it "shorthand", with "1" being "19" and "0" being "20"? This is bad coding, esoteric for no good reason.

Gently, Craig. Ask me to tell the story of the four-page nested IF statement that compiled perfectly and never worked.

DataStage is just not friendly. It gives me many of the same conniptions as Excel, and the leading zero thing is on the short list.

ffsw, I suggest going back to the data format on the mainframe. If it's binary -- COMP or COMP-3 -- then you have no choice but to use DecimalToString and query the value. If it's less than one million -- six digits -- then you'll have to concatenate the zero at the beginning. Something like If, however and as I suspect, it's a display numeric -- no clause at the end of (for example) PIC 9(7) -- then you can change the format of it on the initial read -- the first table definition accepting the input -- to PIC X(7) which Datastage sees as Char(7). It will always have the zero. You can as Craig suggests prefix it with "2" and convert it to the date format you need.

Gently it is, Franklin.

What's the rental fee?

I assign no fault to anyone faced with mainframe and especially Cobol formatted data for the first time or without any background in mainframe development. I've been dealing with EBCDIC to ASCII for 27 years, and it still finds ways to trip me... or is that trap me? One of those...

I hear you. I spend more years than I'd care to mention doing COBOL on minis and then many after that dealing with MF files, EBCDIC, packed fields, etc. Survived Y2K with the goofy "packed dates" we had to fix everywhere... trying to recall the specifics but years ago they had decided to store dates as a YYMMDD decimal, then shove it in a COMP-3... and I seem to recall it ending up as 4 bytes (guess it was signed) and then they only stored the significant 3 bytes as a PIC X(3). With all of the conversion back and forth that entailed every program needing to do, all to "save space". That was quite the mess to clean up.

ffsw13 - did you get this worked out?

Sorry for the late response guys, been drinking from the fire hose lately.. Craig, you were kinda right, once I got a better understanding of the data it all boiled down to: "stick a 20 on the front of it or a 19 to indicate the 20th or 21st century, then just slide a couple of dashes into it." appreciate all the mainframe insight, my COBOL is very rusty at this point