problem inserting DATE type.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: problem inserting DATE type.

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    284

    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

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Try

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

    Look at the NLS_ values in your parameters

    HTH
    Gregg

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Oct 2002
    Posts
    284
    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

  5. #5
    Join Date
    Oct 2002
    Posts
    284
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Apr 2002
    Posts
    36

    This works for me......

    create table table_01
    (column_01 date);


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

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  10. #10
    Join Date
    Oct 2003
    Location
    Faridabad
    Posts
    2
    Try this
    insert into mydate values (to_date('07-JAN-1998', 'DD-MON-YYYY'))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width