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!

Need to add timestamp 23:59:59 to the column containing date (ex: 10/6/2012)

Discussion in 'SQL PL/SQL' started by value2technology, Jun 7, 2012.

  1. Am having a requirement of adding the '23:59:59' to the column of datatype 'date' which contains the date values (ex: 10/6/2012)
    Existing..
    end_date
    -----------
    10/6/2010
    18/3/2011

    Required..
    end_date
    -----------
    10/6/2010 23:59:59
    18/3/2011 23:59:59

    Can anyone help me out..
    thanks in advance
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    If you are using SQL*Plus then you can write query as below just because in SQL*Plus time format is set to 'DD-MON-YY' as default. So you can update easily. Check it by selecting sysdate. If it displays only date you can update it, if its displaying date and time then you need to set date format to only date. You need to alter session as query shown below.

    Code (Text):


    Query1: -
    select sysdate from dual;

    Output: -

    sysdate
    ----------
    07-JUN-2012

    If output is only date then you can skip this query2 and go to query 3 else follow all queries below to set time format and to update record.

    Query2: -
    alter session set nls_date_format='DD/MM/YYYY';
    session altered

    Query3: -
    select empno,ename,hiredate from emp where empno=1;


    Output: -

    empno   ename    hiredate
    -----   -----   ----------
    1   ALLEN   08/06/2012

    Query4: -
    update emp set hiredate=to_date(to_date(hiredate,'dd-mm-yyyy')||' 23:59:59','dd-mm-yyyy hh24:mi:ss') where empno=1;

    1 record updated.

    Query5: -
    select empno,ename,hiredate from emp where empno=1;

    Output: -

    empno   ename    hiredate
    -----   -----   ------------------
    1   ALLEN   08/06/2012 23:59:59

     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The nls_date_format affects ONLY how the date value is displayed in SQL*Plus, not what is stored in the DATE field itself. Simply because the time portion is not displayed by default does not mean the time portion dioesn't exist:

    Code (SQL):
    SQL> SELECT empno, hiredate FROM emp;
     
         EMPNO HIREDATE
    ---------- ---------
          7369 17-DEC-80
          7499 20-FEB-81
          7521 22-FEB-81
          7566 02-APR-81
          7654 28-SEP-81
          7698 01-MAY-81
          7782 09-JUN-81
          7788 09-DEC-82
          7839 17-NOV-81
          7844 08-SEP-81
          7876 12-JAN-83
     
         EMPNO HIREDATE
    ---------- ---------
          7900 03-DEC-81
          7902 03-DEC-81
          7934 23-JAN-82
     
    14 ROWS selected.
     
    SQL>
    SQL> ALTER SESSION SET nls_date_Format = 'DD-MON-RRRR HH24:MI:SS';
     
    SESSION altered.
     
    SQL>
    SQL> SELECT empno, hiredate FROM emp;
     
         EMPNO HIREDATE
    ---------- --------------------
          7369 17-DEC-1980 00:00:00
          7499 20-FEB-1981 00:00:00
          7521 22-FEB-1981 00:00:00
          7566 02-APR-1981 00:00:00
          7654 28-SEP-1981 00:00:00
          7698 01-MAY-1981 00:00:00
          7782 09-JUN-1981 00:00:00
          7788 09-DEC-1982 00:00:00
          7839 17-NOV-1981 00:00:00
          7844 08-SEP-1981 00:00:00
          7876 12-JAN-1983 00:00:00
     
         EMPNO HIREDATE
    ---------- --------------------
          7900 03-DEC-1981 00:00:00
          7902 03-DEC-1981 00:00:00
          7934 23-JAN-1982 00:00:00
     
    14 ROWS selected.
     
    SQL>
    SQL>
     
    The update is also easy to execute:

    Code (SQL):
    SQL> UPDATE emp
      2  SET hiredate = trunc(hiredate)+(86399/86400);
     
    14 ROWS updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT empno, hiredate FROM emp;
     
         EMPNO HIREDATE
    ---------- --------------------
          7369 17-DEC-1980 23:59:59
          7499 20-FEB-1981 23:59:59
          7521 22-FEB-1981 23:59:59
          7566 02-APR-1981 23:59:59
          7654 28-SEP-1981 23:59:59
          7698 01-MAY-1981 23:59:59
          7782 09-JUN-1981 23:59:59
          7788 09-DEC-1982 23:59:59
          7839 17-NOV-1981 23:59:59
          7844 08-SEP-1981 23:59:59
          7876 12-JAN-1983 23:59:59
     
         EMPNO HIREDATE
    ---------- --------------------
          7900 03-DEC-1981 23:59:59
          7902 03-DEC-1981 23:59:59
          7934 23-JAN-1982 23:59:59
     
    14 ROWS selected.
     
    SQL>
     
     
    kiran.marla likes this.
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    If the default format of sysdate is displayed with date and time then we are not able to update the record and showing error as below:

    For this we need to set session as I explained in below post.

    As of your query we need to find out seconds for entire date all the time. We can rewrite your query as shown below:

    Code (Text):

    SQL> UPDATE emp
      2 SET hiredate = trunc(hiredate)+( ( (&HH*60*60) + (&MI*60)+&SS)/86400) where empno = 7369 ;
     
    &HH: 10
    &MI: 30
    &SS: 56
    1 ROW updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT empno, hiredate FROM emp where empno=7369 ;
     
         EMPNO HIREDATE
    ---------- --------------------
          7369 17-DEC-1980 10:33:56

    1 ROW selected.
     
    SQL>
     
    We can enter hiredate's as for our requirement.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is incorrect as my example proves otherwise -- read it again to see how to do the update regardless of the default display format.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    ora-01830 will be raised only when the date you are trying to update is not a valid date format.