1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

date with time details.,

Discussion in 'SQL PL/SQL' started by Vicky, Apr 23, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Can anyone tel me how to insert date along with time details in the date column for a table,..?!


    I have tried this one .,.

    insert into EMPLOYEES
    (DOB)
    values
    (TO_DATE('2014/04/23 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));


    It's inserting, but again showing the Date alone in the table.,

    Tnx.,
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    it is necessary to list all columns with NOT NULL restriction, i.e. mandatory to filling

    Code (SQL):


    CREATE SEQUENCE s_test;
    CREATE TABLE test( id INT PRIMARY KEY ,d DATE);
    INSERT INTO test VALUES(s_test.NEXTVAL,TO_date ('2014/04/23 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));
    INSERT INTO test VALUES(s_test.NEXTVAL,(sysdate));
    commit;
    SELECT TO_char (d, 'yyyy/mm/dd hh24:mi:ss') d FROM test t;

    SQL>
     
    TABLE dropped
     
    TABLE created
     
    1 ROW inserted
     
    1 ROW inserted
     
    Commit complete
     
            ID D                
    ---------- -------------------
            1  2014/04/23 21:02:44
            2  2014/04/23 03:26:38


     
     
    Vicky likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The full date and time data are stored, it's the date FORMAT that is not displaying the values:


    Code (SQL):

    SQL> SELECT sysdate FROM dual;



    SYSDATE
    ---------
    23-APR-14



    SQL> ALTER SESSION SET nls_date_format = 'DD-MON-RRRR HH24:MI:SS';



    SESSION altered.



    SQL> SELECT sysdate FROM dual;

    SYSDATE
    --------------------
    23-APR-2014 12:04:40
    SQL>
     

    I didn't change the data, I changed the display format.
     
    Vicky likes this.