Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    68

    Unanswered: insert date in to table

    hi to all,

    How to insert date in date filed?
    I am trying like this
    Insert into mytable (date1)
    Values (to_date ('20061010121212','YYYYMMDDHHMMSS'));
    It’s giving error.


    Please tell me where how to use to_char and to_date functions with date filed.


    Regards
    Jagan

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Simply, insert DATE (i.e. use TO_DATE function) when you have a "human understandable" date format in order to avoid errors.

    For example, date format we use in Croatia is dd.mm.yyyy - if default NLS settings are different from that, I'll get an error when trying to insert such a format into a DATE column. Therefore, and - even better - ALWAYS use TO_DATE function - don't rely on Oracle's implicit conversion between datatypes.

    On the other hand, you'd use TO_CHAR function to present date (which is stored into the table in Oracle's internal format, and which is always the same) to the end user in format he wants you to: dd.mm.yyyy, dd-mon-yy, mm/dd/yy, etc.
    Code:
    SQL> create table test (col_datum date);
    
    Table created.
    
    SQL> insert into test values (to_date('14.03.2003', 'dd.mm.yyyy'));
    
    1 row created.
    
    SQL> select to_char(sysdate, 'yy/dd/mon') from dual;
    
    TO_CHAR(S
    ---------
    06/15/sep
    
    SQL>

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    As you don't tell us which error you receive I can only assume.

    I guess you want MISS instead of MMSS at the end, as minutes are represented with MI not MM in the format model.

    Check out the manual:
    http://download-west.oracle.com/docs...ts4a.htm#34512

Posting Permissions

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