Hi
I am going to insert Create timestamp & update timestamp for Oracle Table. In the Oracle date type is Date for 2 both the columns
while I am using OCONV I am getting below error.
" while processing column "CRTE_TS"
Value treated as NULL
Attempt to convert String value "2008-7-04" to Date type unsuccessful "
Can any one help
Timestamp convertion needed for Oracl Insert
Moderators: chulett, rschirm, roy
Re: Timestamp convertion needed for Oracl Insert
i think that your input date value is not in proper format,
first you convert your input date value in internal format using ICONV and then use OCONV with below format in which oracle OCI accepts date value for date field
Oconv(@date ,"D D-M-Y[,A3,2]")
first you convert your input date value in internal format using ICONV and then use OCONV with below format in which oracle OCI accepts date value for date field
Oconv(@date ,"D D-M-Y[,A3,2]")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Substrings() is slightly less efficient, because it carries within it the ability to deal with multi-valued data. Prefer Left() and Right() functions or the square bracket notation for substringing.
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.
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
What do you believe the 'correct date and time format' is? Apparently not what Oracle believes it to be.
If you are using generated SQL (as you should be IMHO) and a Timestamp datatype in the job (again, as you should be IMHO) then that format needs to be this:
YYYY-MM-DD HH24:MI:SS
If instead you are using user-defined sql and banking on knowing the NLS default date format of the target database then all bets are off. To me, and this is something I've repeated here ad nauseum, you need to be consistent and always build a full timestamp for an Oracle DATE field and use generated SQL to handle them. Your jobs will then have zero issues with date values and work with any Oracle instance regardless of the NLS_DATE setting.
And yes, that does mean specifically adding " 00:00:00" to a date when you are storing it "without a time".
If you are using generated SQL (as you should be IMHO) and a Timestamp datatype in the job (again, as you should be IMHO) then that format needs to be this:
YYYY-MM-DD HH24:MI:SS
If instead you are using user-defined sql and banking on knowing the NLS default date format of the target database then all bets are off. To me, and this is something I've repeated here ad nauseum, you need to be consistent and always build a full timestamp for an Oracle DATE field and use generated SQL to handle them. Your jobs will then have zero issues with date values and work with any Oracle instance regardless of the NLS_DATE setting.
And yes, that does mean specifically adding " 00:00:00" to a date when you are storing it "without a time".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers