DSXchange

I have a input.json file which has a 'CreatedDate' column with value like below
"CreatedDate":"\/Date(1498684373427)\/", -

Job design

Hiearchical stage---> Transformer--> Oracle stage

1) 'CreatedDate' is coming as 'Varchar' while creating the metadata in Schema Library manager

2)I have used the StageVariable to take only 13 digits using Left and Right function and converted it into StringToDecimal

Please let me know if there is any function in Datastage to convert epoch date (Varchar) to date in target( Oracle).

Welcome!

I don't recall having to work with "epoch" dates in the past and found this site which has a ton of information on the subject including how to do it in Oracle, among other things. So that may be an option for you. Or perhaps custom C++, something UCDI would probably glad to help with.

There's no specific function for handling epoch dates that I've seen or can find this fine morning. Hopefully someone here has done this before and can provide some guidance.

It must be early, and/or the docs call it UNIX time.

TimestampFromTimet
Returns a time stamp from the given UNIX timet string value.
- Input: timet_string (int32)
- Output: timestamp

https://www.ibm.com/support/knowledgece ... tions.html

Thank you, Guys. I will check the link provided by you and get back to you

Not even close to the same thing.

That is an ISO timestamp string with a "T" between the date and time. An epoch date is the number of (milli)seconds since 01/01/1970.

yes, the default time in c++ is the 1/1/70 = 0, so it can work with the format. I have not ever needed to convert that to a real date, but I am sure this is a solved problem either in the language or by someone somewhere like boost.org etc. I actually used cpu clock cycles back when I was doing real time code, so I didnt use either (human dates or time since 1970). Then they made variable speed cpus... sigh.

I can look at it with you if you decide this is the best way to deal with it.

LOL

Thanks. The only example I saw had an input value more like "20090213233130" for that date (which is a little brain-dead) but running your example through that epoch converter site outputs the same external value as in the example, so good.

Just be aware that that is GMT rather than your local time zone, if that matters to you as the date could technically be off by a day, I believe.

Thanks, Guys for your response.

This is what I did below ,but I am facing problem

1) Created a Stage variable(Varchar) to take 10 digits
Left( Right( In_CostConstraintScenarios.CreatedDate,15),10)=svCreatedDate

o/p of Transformer is 1498685212

2) In Transformer stage, I gave TimestampFromTimet(svCreatedDate) ==> datatype in oracle is date......Here I am getting error while compilation Invalid conversion of timestamp to date.

Please let me know what is the mistake I am doing.

Solved the problem.

Steps Followed
1) Stage Variable -
svCreatedDate(Varchar)- Left( Right( In_CostConstraintScenarios.CreatedDate,15),10)

2) Created one more Stage variable 'svCD' as Integer with value 'AsInteger(svCreatedDate)'

3)Transformer1 stage
TimestampFromTimet(svCD)-Varchar---> CreatedDate

4) Transformer2 Stage
StringToDate(Out_CostConstraintScenarios.CreatedDate)

5) Loaded as date in Oracle stage
o/p==>6/28/2017 12:00:00 AM