converting varchar to timestamp in Oracle-ORA-01850 error

sheema · Post by **sheema** » Wed Jan 03, 2007 5:10 pm

Hi ,

I have a job where in my source is sequential file and target is OraOCI.
in my seq file the data type for date field is varchar,i need to convert this into timestamp and than just take the date part of it(first 10 characters).

I have selected the datatype Timestamp for OraOCI.I have used Generated SQL in the ORAOCI,so it says TO_TIMESTAMP(x,"YYYY-MM-DD HH24:MI:SS").I have declared length of 10 for this timestamp,so that i can just take the date part.
I see warning in the director saying ORA-01850 hours must be between 0 and 23.

Any help is greatly appreciated.

Thanks
sheema

chulett · Post by **chulett** » Wed Jan 03, 2007 5:14 pm

Post a sample of your data and the actual derivation you are using.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 03, 2007 5:14 pm

Make the length 19 and supply an all-zeros time part. Your date picture indicates that you are supplying date and time components.

Code: Select all

InLink.VarCharDate : " 00:00:00"

chulett · Post by **chulett** » Wed Jan 03, 2007 8:42 pm

Actually, generated SQL would give you a TO_DATE function with the mask that you noted. And as noted, simply tack on the zero time that the function requires if all you are getting is a date in the correct format.

sheema · Post by **sheema** » Thu Jan 04, 2007 9:44 am

My source is coming as mm/dd/yyyy which is varchar , now they say that the target is a oracle table and it has to be converted into date.

so in the transformer i am using OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-DMY[2,A3,2]"). In the ORAOCI stage i am using a user defined sql and
i am not doing any TO_DATE for the date field.
But i am getting error
ORA-01400: cannot insert NULL into Date Field.

Please help me out.

Thanks
Sheema

chulett · Post by **chulett** » Thu Jan 04, 2007 9:51 am

Knock it off with the User Defined, let the stage generate the sql for you unless you've got absolutely no other choice. Define it in the job as a size 19 Timestamp and then format the date as the sql expects it:

YYYY-MM-DD HH24:MI:SS

Use something very much like this to achieve that:

Code: Select all

OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00"

And you'll be fine.

wnogalski · Post by **wnogalski** » Thu Jan 04, 2007 9:55 am

DATE datatype has nothing to do with the data format you get when you issue a SELECT statement - the format You see depends on your client settings.
Oracle performs some datatype conversions automatically but it's better to do it explicity.
If you have a varchar datatype in your OCI stage then you will insert strings. Switch into User-defined SQL and correct yout insert statement to use TO_DATE function to conver VARCHAR into DATE. Search Oracle manusals or Google for more info about TO_DATE function.
You can also use Craig's solution but remember set the datatype to Timestamp for your date column - the generated SQL will contain TO_DATE with proper arguments.
ORA-01400 error means You're trying to insert NULL value into column which doesn't allow NULLs or the implicit conversion doesn't work properly.

sheema · Post by **sheema** » Thu Jan 04, 2007 10:21 am

Hi Chulett,

I have changed as you suggested the datatype for target date as timestamp 19
used
OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00".

I have used user defined Sql, where i am not doing any conversion.

I am getting error

ORA-00936: missing expression
DBMS.CODE=ORA-00936

can you help me out

Thanks
sheema

chulett · Post by **chulett** » Thu Jan 04, 2007 10:28 am

sheema wrote:I have used user defined Sql, where i am not doing any conversion.

You have to follow all of the suggestions, not just some. Let the stage generate the sql.

sheema · Post by **sheema** » Thu Jan 04, 2007 10:33 am

I am sorry,it missed that part.
It is working fine now.
Thanks for the needy help.

Sheema