Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Changing the datetime - 9i

    Hi guys

    I have a table in which the datetime values are not trunced (if I can use the word) to the second.

    Code:
    i.e., 05/06/2012 00:00:01
          05/06/2012 00:15:01
          05/06/2012 00:30:01
    etc

    Is there a way to change these values to:

    Code:
    i.e., 05/06/2012 00:00:00
          05/06/2012 00:15:00
          05/06/2012 00:30:00
    Regards
    Shajju

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Do you want to simply remove the seconds or round the values up to the next quarter? e.g. 00:28:17 --> 00:30:00

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    No, I don't want to round the values up to the next quarter.

    Just want change '01' to '00' for the seconds for all the rows in the 'DATETIME' column please.

    Regards
    Shajju
    Last edited by shajju; 06-06-12 at 04:06.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    There is no "datetime" datatype in Oracle, so I assume you actually mean "DATE".

    Code:
    update the_unknown_table
        set the_date_column = to_date(to_char(the_date_column, 'yyyy-mm-dd hh24:mi')||':00', 'yyyy-mm-dd hh24:mi:ss')

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Cool

    Ah! TRUNC works so much better. All Oracle developers should make themselves familiar with the date format models and use them more often.
    Code:
    imgx@ORCL> alter session set nls_date_format="dd MON yyyy hh24:mi:ss";
    
    Session altered.
    
    imgx@ORCL> select trunc(sysdate, 'MI') from dual;
    
    TRUNC(SYSDATE,'MI')
    --------------------
    06 JUN 2012 10:17:00
    
    imgx@ORCL> select trunc(sysdate, 'HH24') from dual;
    
    TRUNC(SYSDATE,'HH24'
    --------------------
    06 JUN 2012 10:00:00
    
    imgx@ORCL> select trunc(sysdate, 'DD') from dual;
    
    TRUNC(SYSDATE,'DD')
    --------------------
    06 JUN 2012 00:00:00
    
    imgx@ORCL> select trunc(sysdate, 'DAY') from dual;
    
    TRUNC(SYSDATE,'DAY')
    --------------------
    03 JUN 2012 00:00:00
    
    imgx@ORCL>

  6. #6
    Join Date
    Aug 2008
    Posts
    464

    ManyThanks

    Excellent, thanks.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by dayneo View Post
    Ah! TRUNC works so much better.
    Right. I always forget that trunc can also be called with a format mask...

Posting Permissions

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