HOw to insert date in oracle
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 149
- Joined: Fri Oct 15, 2004 12:13 am
- Location: Toronto,divine_auro@yahoo.co.in
- Contact:
HOw to insert date in oracle
Dear Team,
While inserting the date from file to oracle database I got the following error.
Attempt to convert String value "31-DEC-2004" to Date type unsuccessful
I am using the query
INSERT INTO Test_Tab (EMP_ID,JOIN_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'))
Can any one please tell me where I am wrong.
While inserting the date from file to oracle database I got the following error.
Attempt to convert String value "31-DEC-2004" to Date type unsuccessful
I am using the query
INSERT INTO Test_Tab (EMP_ID,JOIN_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'))
Can any one please tell me where I am wrong.
With Regards
Biswajit
Biswajit
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What stage type are you using? Have you set the Data Element for the JOIN_DATE column? Is it generated or user-written SQL? What is the data type of both columns? Did you import the table definition and use the imported table definition? All of these factors affect the answer.
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: 149
- Joined: Fri Oct 15, 2004 12:13 am
- Location: Toronto,divine_auro@yahoo.co.in
- Contact:
Dear Ray
my job is :
Sequential stage -> Transformer ->OCI Stage( Target is Oracle 9i Table).
the source date format is integer ( eg:20042131) and has to go as is to the target. I have use the following transfromer to convert it to a date.
UpCase(Oconv(Iconv(DSLink4.JoinDate,"DYMD"),"D-DMY[,A3,2]"))
Ihave also check the out put and it comes as eg. 31-DEC-04. While inserting it into Oracle I am using the query
INSERT INTO Test_Tab (EMP_ID,JOIN_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY')) .
While inserting the date from file to oracle database I got the following error.
Attempt to convert String value "31-DEC-04" to Date type unsuccessful
Can you please tell me where I am wrong ?
my job is :
Sequential stage -> Transformer ->OCI Stage( Target is Oracle 9i Table).
the source date format is integer ( eg:20042131) and has to go as is to the target. I have use the following transfromer to convert it to a date.
UpCase(Oconv(Iconv(DSLink4.JoinDate,"DYMD"),"D-DMY[,A3,2]"))
Ihave also check the out put and it comes as eg. 31-DEC-04. While inserting it into Oracle I am using the query
INSERT INTO Test_Tab (EMP_ID,JOIN_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY')) .
While inserting the date from file to oracle database I got the following error.
Attempt to convert String value "31-DEC-04" to Date type unsuccessful
Can you please tell me where I am wrong ?
With Regards
Biswajit
Biswajit
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Maybe it's just your typing, but 20041231 is not a valid date (in ANY format), which might explain why you're having a problem.
Certainly you can convert a valid date in YYYYMMDD format into internal format using, and convert this into any required external format using Oconv() - for example But, if you don't have valid dates on input, all bets are off.
Certainly you can convert a valid date in YYYYMMDD format into internal format using
Code: Select all
Iconv(TheDate, "DYMD")
Code: Select all
Oconv(intDate, "D-YMD[2,A3,4]")
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.
Re: HOw to insert date in oracle
In the original post, the input date is 31-DEC-2004 (4 digit year), but the Oracle to_date format string is 'DD-MON-YY' (2 digit year). This will certainly yield an error.
Meanwhile, how is 20041231 not a valid date?
(year=2004, month=12, day=31)
Carter
Meanwhile, how is 20041231 not a valid date?
(year=2004, month=12, day=31)
Carter
divine wrote:Dear Team,
While inserting the date from file to oracle database I got the following error.
Attempt to convert String value "31-DEC-2004" to Date type unsuccessful
I am using the query
INSERT INTO Test_Tab (EMP_ID,JOIN_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'))
Can any one please tell me where I am wrong.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
No, it's the DB2 stage that requires a date in internal format.
The OCI stage is driven by the data type associated with the DATE field in the job. There is even a special section in the pdf documentation for the OCI stages on Date Handling.
Typically, you declare the DATE field to be a Timestamp in the job and make sure you get your data to it in YYYY-MM-DD HH24:MI:SS format. Adopt that as a standard, build some simple routines to automate that for you, make sure everyone uses them and you'll never have problems with Oracle dates again.![Wink :wink:](./images/smilies/icon_wink.gif)
The OCI stage is driven by the data type associated with the DATE field in the job. There is even a special section in the pdf documentation for the OCI stages on Date Handling.
Typically, you declare the DATE field to be a Timestamp in the job and make sure you get your data to it in YYYY-MM-DD HH24:MI:SS format. Adopt that as a standard, build some simple routines to automate that for you, make sure everyone uses them and you'll never have problems with Oracle dates again.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
wdudek wrote:That and formatting the "date" field to look like 13-JUN-05 should let it go right into oracle.
![Exclamation :!:](./images/smilies/icon_exclaim.gif)
I prefer a method that works regardless of the default date format. Maybe it's because I've worked in places where the DBA has decided to change it for whatever reason. Man, does that wreak havoc on processes that assume they know what it is.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers