problem inserting DATE type.

Hello all..

I have Table like this..

SQL> desc mydate;
Name Null? Type
----------------------------------------- -------- ---------------
DATE1 DATE


When i try to insert date using INSERT statement, i am getting an error. I have the date in 7/1/1998 format.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> insert into mydate values to_date('7/1/1998', 'mm/dd/yyyy');
insert into mydate values to_date('7/1/1998', 'mm/dd/yyyy')
*
ERROR at line 1:
ORA-03001: unimplemented feature
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

How do i insert this date into my database ?...


thanks
Ron

Try

SQL> insert into mydate values to_date('7/1/1998', 'DD-MON-YYYY');

Look at the NLS_ values in your parameters

HTH
Gregg

parentheses missing ...

Code:

---

insert into mydate values (to_date('7/1/1998', 'DD-MON-YYYY'));

---

I assume there's only one column in the mydate table?

Well..i tried to do exactly what you said...

+++++++++++++++++++++++++++++++++++++++++++++++++
SQL> insert into mydate values to_date('7/1/1998', 'DD-MON-YYYY');
insert into mydate values to_date('7/1/1998', 'DD-MON-YYYY')
*
ERROR at line 1:
ORA-03001: unimplemented feature


SQL> show parameter nls_

NAME TYPE VALUE
------------------------------------ ----------- --------------------
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string

+++++++++++++++++++++++++++++++++++++++

any suggestions ?..

Thanks
Ron

Well i also tried what slimdave suggested ...

++++++++++++++++++++++++++++++++++++++++++++++++
SQL> insert into mydate values (to_date('7/1/1998', 'DD-MON-YYYY'));
insert into mydate values (to_date('7/1/1998', 'DD-MON-YYYY'))
*
ERROR at line 1:
ORA-01843: not a valid month

++++++++++++++++++++++++++++++++++++++++++++++++


Yes there is only one Col in this table...This is a dummy table..

Thanks
Ron

small date format error

Code:

---

insert into mydate values (to_date('7/1/1998', 'DD-MM-YYYY'));

---

... or ...

Code:

---

insert into mydate values (to_date('7/1/1998', 'MM-DD-YYYY'));

---

...depending which way round your format is.

create table table_01
(column_01 date);


insert into table_01 values (to_date('07/01/1998', 'MM/DD/YYYY'))
:)

Yes - 2 major rules broken here:

1 - Never assume a column list. ie., never do SELECT * and never do INSERT *without* a column list

2 - Never assume a date format. (and for pete's sake, when specifying a format for a value, try to make the format match the value - no kidding that to_date('7/1/1998', 'DD-MON-YYYY')) is going to give an error - does 7/1/1998 look like a DD-MON-YYYY format? Sheesh)

- Chris

Absolutely.
Seemed such a basic error that I'm surprised it took 5 or 6 posts before anyone pointed it out.

Sheesh indeed!

And its hardly a DBA topic is it?

Try this
insert into mydate values (to_date('07-JAN-1998', 'DD-MON-YYYY'))

Quote:

---

Originally posted by JMac
Absolutely.
Seemed such a basic error that I'm surprised it took 5 or 6 posts before anyone pointed it out.

Sheesh indeed!

And its hardly a DBA topic is it?

---

The original error was the missing parentheses, not the date format.