I'm trying to convert varchar (40) data from an Oracle DB source on unix to date data format for an oracle output table.
in a DS transform, in an Xmap job
input is varchar (40) - needs to convert in DS to a timestamp value that will be inserted to oracle table date column in a DS insert job.
I do not see StringtoDate listed in the function drop down list - or in DS help.
Not sure if that is the appropriate route.
thanks
varchar to date conversion on DS 7.5
Moderators: chulett, rschirm, roy
The Oracle OCI9 stage automatically puts a TO_DATE(yourcolumn, 'YYYY-MM-DD HH24:MI:SS') statement on the SQL for that column when using auto-generated SQL on DATE datatype columns. All you have to do is make sure your date is in that form, if it is not, there are posted functions on this forum that can assist you in formulating it. If you're reading a sequential text file output from Oracle, hopefully it's already in that format and is ready to load.
Just play with the metadata and inspect the generated SQL to see the effects. If you're using user-defined SQL, you have to take care of the TO_DATE stuff yourself.
Just play with the metadata and inspect the generated SQL to see the effects. If you're using user-defined SQL, you have to take care of the TO_DATE stuff yourself.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
thanksclshore wrote:Use an Oracle Date formatter like 'MONTH DD YYYY HH:MI AM' with your to_date().
Carter
but we can't guarantee it will be AM
could be AM or PM
Isn't there a way in a a data stage transform - in the derivation for the row - to convert from whatever the string character data is to a date /timestamp fomat? is StringtoDate supported in data stage?
We'd feel safer if we knew it was converted BEFORE the insert to the table - to avoid fatal job errors on bad data that might not make it
thanks
-
- Participant
- Posts: 85
- Joined: Fri Jun 04, 2004 2:30 am
- Location: Melbourne, Australia
- Contact:
If you need to convert the date within Datastage you will need to change the incoming format into internal datastage formatting. Something like this:
This will result in your date coming out as YYYY-MM-DD.
If you need the time component you'll have to add the code to do that.
Code: Select all
Oconv(IConv(Arg1[" ",1,3], "D MDY[A,2,4]"), "D-YMD[4,2,2]")
If you need the time component you'll have to add the code to do that.
Cheers,
Dave Nemirovsky
Dave Nemirovsky
Carter's method should work just fine - if you have complete faith in the validity of the incoming data:RC99 wrote:thanks, but we can't guarantee it will be AM, it could be AM or PM
Code: Select all
TO_DATE(YourField,'MONTH DD YYYY HH:MI AM')
If you really want to check the validity of your dates, you'll need a custom routine.RC99 also wrote:We'd feel safer if we knew it was converted BEFORE the insert to the table - to avoid fatal job errors on bad data that might not make it
In a routine, you can use the STATUS function to check each step of the conversions to see if they were successful. For instance, do the Iconv in a separate step and check the status to know if it was converted. Then do the OConv in another step (assuming success) to get it in the proper output format for the OCI stage.
Just doing the Iconv/ Oconv conversion inside the derivation won't protect you from bad date values, you'd still get 'fatal job errors on bad data'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers