converting varchar to timestamp in Oracle-ORA-01850 error
Moderators: chulett, rschirm, roy
converting varchar to timestamp in Oracle-ORA-01850 error
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
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:
And you'll be fine.
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"
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Regards,
Wojciech Nogalski
Wojciech Nogalski
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
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