varchar to date conversion on DS 7.5

[RC99]

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

[kcbland]

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.

[RC99]

our data comes in as a text varchar filed, not a date field
I did a trim on it and ran it throught the insert job, but To_Date in Ora stage does not accept it
data is varchar looking like this

DECEMBER 29 1899 12:00 AM

[clshore]

Use an Oracle Date formatter like 'MONTH DD YYYY HH:MI AM' with your to_date().

Carter

[RC99]

Use an Oracle Date formatter like 'MONTH DD YYYY HH:MI AM' with your to_date().

Carter

thanks
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

[davidnemirovsky]

If you need to convert the date within Datastage you will need to change the incoming format into internal datastage formatting. Something like this:

Code: Select all

Oconv(IConv(Arg1[" ",1,3], "D MDY[A,2,4]"), "D-YMD[4,2,2]")

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.

[chulett]

thanks, but we can't guarantee it will be AM, it could be AM or PM

Carter's method should work just fine - if you have complete faith in the validity of the incoming data:

Code: Select all

TO_DATE(YourField,'MONTH DD YYYY HH:MI AM')

In your target stage, probably via Custom SQL. The 'AM' in the date formatting doesn't mean it only takes AM times, it means you will be supplying your times with 'AM' or 'PM' at the end, and will default to AM if you don't specify which it is. The alternative is to use military time and use 'HH24' for the hours portion of the mask, leaving off the 'AM' bit.

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

If you really want to check the validity of your dates, you'll need a custom routine.

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