Epoch date conversion ( .json) to Oracle Date
Moderators: chulett, rschirm, roy
Epoch date conversion ( .json) to Oracle Date
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).
"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).
Suja
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
Choose a job you love, and you will never have to work a day in your life. - Confucius
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.
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. 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Suja
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
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
Suja