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!

Trunc timestamp

Discussion in 'SQL PL/SQL' started by krithika@2001, Jul 31, 2009.

  1. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    hI

    I have a particular query.

    i have a field called begin_date '07/23/1985 00:00:00'.I need to truncate the timestamp and just get the date.

    If i use
    select trunc(to_date('07/23/1985 00:00:00','mm/dd/yyyy HH:MI:SS'))

    I am getting ORA error as hour should be between 1 to 12.But i need to truncate the timestamp.Is there any way?Please let me know

    Regards
    Krithika
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    SQL> SELECT trunc(to_date('07/23/1985 00:00:00','mm/dd/yyyy HH:MI:SS')) FROM d
    l;
    SELECT trunc(to_date('07/23/1985 00:00:00','mm/dd/yyyy HH:MI:SS')) FROM dual
                         *
    ERROR at line 1:
    ORA-01849: HOUR must be BETWEEN 1 AND 12


    SQL> SELECT trunc(to_date('07/23/1985 00:00:00','mm/dd/yyyy HH24:MI:SS')) FROM
    ual;

    TRUNC(TO_
    ---------
    23-JUL-85

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

    SESSION altered.

    SQL> SELECT trunc(to_date('07/23/1985 00:00:00','mm/dd/yyyy HH24:MI:SS')) FROM
    ual;

    TRUNC(TO_DATE('07/23
    --------------------
    23-JUL-1985 00:00:00

    SQL>
    Is it what you want to try ?
    See.. Still you will have time attatch with Date . Its alwyas there in Oracle !!!
     
  3. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Thanks a lot raj.That was amazing.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    there you gave just HH but you should give HH24