problem inserting DATE type.

[vr76413]

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

[gbrabham]

Try

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

Look at the NLS_ values in your parameters

HTH
Gregg

[slimdave]

parentheses missing ...

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

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

[vr76413]

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

[vr76413]

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

[slimdave]

small date format error

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

... or ...

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

...depending which way round your format is.

[xuduro]

create table table_01
(column_01 date);


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

[chrisrlong]

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

[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?

[Naveen Dutt]

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