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!

How to add hours to a converted time.,,?!

Discussion in 'SQL PL/SQL' started by Vicky, Sep 3, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    While executing the query below:

    SELECT TO_CHAR(to_date('21-aug-79 15:36', 'dd/mm/rr hh24:mi'), 'hh24:mi')+1 FROM DUAL


    I'm facing the error below:
    ORA-01722: invalid number
    01722. 00000 - "invalid number"
    *Cause:
    *Action:
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    SELECT TO_CHAR(to_date('21-aug-79 15:36', 'dd/mm/rr hh24:mi'), 'hh24') + 1 FROM DUAL
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Just to complement above post:

    Vicky, I suppose your requirement is just to add 1hr to the hour portion of the date and get the updated hour only - in which this is Ok.

    However, if your requirement is to increment the time for a given date by 1 and get the updated date+time, then you have an issue(illustrated by the following SQL statements):

    Code (SQL):
    SQL> -- Just to display the date+hour by default
    SQL> ALTER SESSION SET nls_date_format='DD/MM/RR hh24:mi';

    SESSION altered.

    SQL> SELECT TO_DATE(TO_CHAR(to_date('21-apr-79 15:36', 'dd/mm/rr hh24:mi'), 'hh24') + 1, 'hh24') new_date FROM DUAL;

    NEW_DATE
    --------------
    01/09/14 16:00

    SQL> -- updated hour "attached" by default to 1st day of month

    SQL> SELECT TO_DATE('21-apr-79 15:36', 'dd/mm/rr hh24:mi')+1/24 new_date FROM DUAL;

    NEW_DATE
    --------------
    21/04/79 16:36

    SQL> -- 1hr added to your supplied date
     
    Vicky likes this.