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

    Unanswered: datetime concept unclear

    Hi

    I really need your help.

    I wanted to select for between the hours: 00:00 and 10:59 for the last day so I used:

    WHERE a.DATETIME between trunc(sysdate-1,'dd')+0/24 and trunc(sysdate-1,'dd')+10.9/24

    But instead of the expected 6 rows, I got 66. I was getting 6 rows with:

    WHERE a.DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600

    Query

    Code:
    (SELECT  trunc(datetime,'hh') datetime, substr(MSC,3)MSC, round(DECODE(sum(NSCAN),0,0,max(ACCLOAD)/max(NSCAN)),2) CP_USAGE
    
    
    FROM ERICSSON_NSS.LOAS A
    
    WHERE  a.DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600 
    
    --WHERE  a.DATETIME between trunc(sysdate-1,'dd')+0/24 and trunc(sysdate-1,'dd')+10.9/24 
    
    AND ((MSC = 'OMHLR3') OR (MSC = 'OMMSC10') OR (MSC = 'OMMSC3') OR (MSC = 'OMMSC4') OR (MSC = 'OMMSC7') OR (MSC = 'OMMSC8') OR (MSC = 'OMMSC9'))
    
    group by trunc(datetime,'hh'), msc
    )

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    looks self explanatory to me:
    PHP Code:
      1select trunc(sysdate,'hh')-1/24 betw1,  trunc(sysdate,'hh')-1/24/3600 betw2 from dual
    duck
    @dev01> /

    BETW1        BETW2
    ------------ ------------
    091609 13:00 091609 13:59 
    looks like you really want:
    PHP Code:
    duck@dev01select trunc(sysdatebetw1trunc(sysdate)+10/24+59/1440 betw2 from dual;

    BETW1        BETW2
    ------------ ------------
    091609 00:00 091609 10:59 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    as i said on your other string

    dont forget the 60 seconds between 10:59 and 11:00. use

    Code:
    >alter session set nls_date_format='mmddyy hh24:mi.ss';
    
    Session altered.
    
    >select trunc(sysdate) betw1, trunc(sysdate)+(11/24)-(1/86400) betw2 from dual;
    
    BETW1           BETW2
    --------------- ---------------
    091609 00:00.00 091609 10:59.59
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I prefer to use the INTERVAL data type for things like that because it makes things a lot clearer:
    Code:
    SELECT *
    FROM the_table
    WHERE a.datetime
       BETWEEN trunc(sysdate) 
           AND trunc(sysdate) + interval '10:59:59' hour to second
    I'm not sure what you meant with "for the last day". Is that "today" or yesterday?

    If you meant "yesterday", then you'll need something like this:
    Code:
    SELECT *
    FROM the_table
    WHERE a.datetime
       BETWEEN trunc(sysdate - 1) 
           AND trunc(sysdate - 1) + interval '10:59:59' hour to second

  5. #5
    Join Date
    Aug 2008
    Posts
    464

    Thankssss

    Super guys. Really appreciate clearing my concepts.

    Just one last one. Using Toad...

    When I trunc(sysdate), I don't see 00:00:00....???

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju
    Just one last one. Using Toad...

    When I trunc(sysdate), I don't see 00:00:00....???
    I don't use Toad, but I recall a co-worker of mine who mentioned that there is an option to hide a time part of 00:00:00 inside Toad.
    You'll need to read the manual

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Use TO_CHAR function with an appropriate format mask and you'll see it.

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    OK, it's a bit more complicated.

    I used:

    Code:
    select trunc(sysdate,'dd') aa,trunc(sysdate,'dd')+11/24-1/86400 bb from dual
    and got:

    AA BB
    9/17/2009 (no 00:00:00) 9/17/2009 10:59:59 AM

    Another thing which must have something to do with the setting on my db is that I used 'substr' to only get the time portion from the result from:

    Code:
    select trunc(sysdate,'dd') START_DATETIME,substr(trunc(sysdate,'dd')+11/24-1/86400,1) END_DATETIME from dual
    but what happened was that the time portion got truncated and the format of the date changed from mm/dd/yyyy to dd/mm/yyyy.

    I tried the same on another PC but this time starting from the 8th character and got the time perfectly.

    If I start from the 8th character on this PC, I get nothing back.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Gee, you just won't listen. TO_CHAR is to be used, not SUBSTR.
    Code:
    SQL> select to_char(trunc(sysdate, 'dd') + 11/24-1/86400, 'hh24:mi:ss') from dual;
    
    TO_CHAR(
    --------
    10:59:59

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Littlefoot
    Gee, you just won't listen. TO_CHAR is to be used, not SUBSTR.
    Code:
    SQL> select to_char(trunc(sysdate, 'dd') + 11/24-1/86400, 'hh24:mi:ss') from dual;
    
    TO_CHAR(
    --------
    10:59:59
    But, using this, you will get a VARCHAR2. It might be good for displaying purposes in TOAD, however it may not be suitable for another processing.

    Anyway, I would take shammat's advice:
    Quote Originally Posted by shammat
    You'll need to read the manual
    I do not doubt that searching "date format" will find a description how to set it in TOAD (although I do not have it).
    In SQL/Developer, this setting is available in Database -> NLS Settings tab under Tools -> Preferences menu.
    And after all, you may every time set NLS_DATE_FORMAT manually using ALTER SESSION command. Again, documented in Oracle Database SQL Reference book.

    [Edit: Mentioned TOAD]

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by shajju
    Super guys. Really appreciate clearing my concepts.

    Just one last one. Using Toad...

    When I trunc(sysdate), I don't see 00:00:00....???
    GO INTO TOAD SETTINGS
    CHANGE DATE DEFAULT DISPLAY FORMAT
    done
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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