Convert MF Date CYYMMDD to DD-MMM-YY:00:00:00

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

Post Reply
ffsw13
Premium Member
Premium Member
Posts: 31
Joined: Mon Feb 01, 2016 10:29 am
Location: USA
Contact:

Convert MF Date CYYMMDD to DD-MMM-YY:00:00:00

Post by ffsw13 »

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
The UNIVERSE is vast and expansive
I never Metadata I didn't like
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ffsw13
Premium Member
Premium Member
Posts: 31
Joined: Mon Feb 01, 2016 10:29 am
Location: USA
Contact:

Post by ffsw13 »

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.
The UNIVERSE is vast and expansive
I never Metadata I didn't like
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

Franklin trots out his Cobol soapbox... :wink:

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.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

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

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

Code: Select all

If inputDecimalValue < 1000000 then "20" : StringValue Else "2" :  StringValue
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.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Gently it is, Franklin. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

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... :lol:
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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". :roll: That was quite the mess to clean up.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

ffsw13 - did you get this worked out?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ffsw13
Premium Member
Premium Member
Posts: 31
Joined: Mon Feb 01, 2016 10:29 am
Location: USA
Contact:

Post by ffsw13 »

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 :roll: :wink:
The UNIVERSE is vast and expansive
I never Metadata I didn't like
Post Reply