First suggest would be to find out what your example dates translate to so we can get some idea what the conversion might be. Are these real examples of your incoming data?
-craig
"You can never have too many knives" -- Logan Nine Fingers
Use arithmetic (including Int() and Mod() functions) to extract the "year", month and day, add 1900 to the "year", then use DateFromComponents() function to re-assemble them.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Or add 19000000 to the decimal to get a number in YYYYMMDD format, then DecimalToString() gets you close. After that, do you need a DATE or a STRING in the (external) format shown?
-craig
"You can never have too many knives" -- Logan Nine Fingers
... as noted, assuming a DATE is actually what is wanted. Many times when we see a 'date' shown with specific formats needed, what they're really looking for is a string.
-craig
"You can never have too many knives" -- Logan Nine Fingers
i tried this logic while extracting from source table date(timestamp_format(char(DTE+19000000), 'YYYYMMDD')) DTE and date as datatype
but it is throwing a warning as Expecting:symbol of type:date_string
My target is date datatype. i can use which ever possible way through datastage or writing sql. I wanted to write logic in datastage but then i thought its complicated and choose to write sql.
my source is AS400
Do the math in DataStage and then DecimalToString() as I wrote and then use the function as noted by vasudev_koti. That should be all there is to it and you can embed the functions together... or do them in steps via stage variables if desired.
And I would drive that decision based on how valid your incoming decimal date values are.
-craig
"You can never have too many knives" -- Logan Nine Fingers
