DSXchange

Hi,

I have a scenario where i need to convert decimal to date:

Ex: id dte
1 1000728
2 960912


Target

id dte
1 yyyy-mm-dd
2 yyyy-mm-dd


Any suggestions?

Thanks

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?

Hi Chulett,

yes these are real dates comming source.

i am expecting o/p as

Target
id dte
1 2000-07-28
2 1996-09-12

So... the last four digits are MMDD and anything before that can be added to 1900 to get the YYYY is what it looks like to me.

may i know the exact function to be used to add ?

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.

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?

HI
You have to Use StringToDate(Src.ColumnName, '%YYYY%MM%DD') Outut Data type as Date
Thanks
Vasu

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

Hi Chulett,

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

Thanks

So, you are trying to do this in SQL rather than DataStage? If so, what database?

And you still haven't clarified if your target is an actual date datatype or a 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.