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

    Unanswered: truncating the seconds - 10g

    Hi

    I have a table which has 1000 rows in the datetime type column containing hourly values like:

    Code:
    DATETIME
    
    12/11/2013 04:00:07
    12/11/2013 04:00:07
    12/11/2013 04:00:06
    12/11/2013 04:00:05
    12/11/2013 04:00:04
    12/11/2013 04:00:03
    12/11/2013 04:00:02
    12/11/2013 04:00:01
    12/11/2013 03:45:07
    12/11/2013 03:45:07
    12/11/2013 03:45:06
    12/11/2013 03:45:05
    12/11/2013 03:45:04
    12/11/2013 03:45:03
    12/11/2013 03:45:02
    12/11/2013 03:45:01
    12/11/2013 03:30:07
    12/11/2013 03:30:07
    12/11/2013 03:30:06
    12/11/2013 03:30:05
    12/11/2013 03:30:04
    12/11/2013 03:30:03
    12/11/2013 03:30:02
    12/11/2013 03:30:01
    12/11/2013 03:15:07
    12/11/2013 03:15:07
    12/11/2013 03:15:06
    12/11/2013 03:15:05
    12/11/2013 03:15:04
    12/11/2013 03:15:03
    12/11/2013 03:15:02
    12/11/2013 03:15:01
    12/11/2013 03:00:07
    12/11/2013 03:00:07
    12/11/2013 03:00:06
    12/11/2013 03:00:05
    12/11/2013 03:00:04
    12/11/2013 03:00:03
    12/11/2013 03:00:02
    12/11/2013 03:00:01
    12/11/2013 02:45:07
    ........and so on.
    ie. the seconds are not
    Code:
     00
    Could someone please advise how to update this column to truncate the seconds in all the rows?

    Regards
    Shajju
    Last edited by shajju; 11-12-13 at 08:23.

  2. #2
    Join Date
    Dec 2007
    Posts
    253

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thank you Pablolee. How would I incorporate this into the update statement for the datetime column?

    something like:
    Code:
    update table set datetime='trunc(datetime,'mi')'
    Last edited by shajju; 11-13-13 at 01:28.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
    
    Session altered.
    
    SQL> SELECT SYSDATE FROM DUAL;
    
    SYSDATE
    -------------------
    2013-11-12 20:53:21
    
    SQL> SELECT TRUNC(SYSDATE,'MI') FROM DUAL;
    
    TRUNC(SYSDATE,'MI')
    -------------------
    2013-11-12 20:53:00
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Thanks anacedent.

    I understand the concept but was having difficulty using it to update the values of a column.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Just curious: are you really unable to spot the difference (except different date column name) between the expression
    Code:
    trunc(sysdate,'mi')
    and the string literal (to be exact, two literals separated by two characters - mi)
    Code:
    'trunc(datetime,'mi')'
    ? If you are trying to construct a dynamic SQL statement, well, your post does not contain any remark of that, so I hope this is not the case and you will forget it.

Posting Permissions

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