HOw to insert date in oracle

[divine]

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.

[ray.wurlod]

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.

[divine]

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 ?

[divine]

Dear Roy ,

the source date format is integer ( eg:20041231)

[Sainath.Srinivasan]

First you need to define the date as a char.

Second you need to ensure that both the format and content are identical.

[ray.wurlod]

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

Code: Select all

Iconv(TheDate, "DYMD")

, and convert this into any required external format using Oconv() - for example

Code: Select all

Oconv(intDate, "D-YMD[2,A3,4]")

But, if you don't have valid dates on input, all bets are off.

[clshore]

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

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.

[ray.wurlod]

Search the Forum; I have a vague recollection that the OCI stage requires an internal format date.

[chulett]

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.

[wdudek]

Did you resolve the problem? The person who mentioned changing the date to a char should be correct, although we use varchar. That and formatting the "date" field to look like 13-JUN-05 should let it go right into oracle.

[chulett]

That and formatting the "date" field to look like 13-JUN-05 should let it go right into oracle.

Only if that is the current NLS_DATE_FORMAT for the target database. Granted, DD-MON-RR is the default out of the box, but still...

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.

[wdudek]

Ouch!! A DBA changing he default date format would definately cause problems with my assumption.