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

    Unanswered: displaying the datetime of your choice

    Hi

    Could someone please help me display the datetime of my choice?

    How can I display the time of my choice? How can I use the TO_CHAR function?

    like e.g., sysdate-1 (00:00 - 10:59) or even just (00:00 - 10:59) ?

    Regards
    Shajju

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Ok how are you getting your "choice"??? You are going to have to call to_char twice with two different dates.

    Code:
    SELECT to_char(dt1, 'HH24:MI') || ' - ' || to_char(dt2, 'HH24:MI')
    FROM myObscureChoice
    
    or
    
    SELECT to_char(dt1, 'HH24:MI') || ' - ' to_char(dt1 + 11/24, 'HH24:MI')
    FROM myObscureChoice

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks for replying.

    Basically, I'm choosing data for a period using:

    Code:
    WHERE DATETIME between TRUNC(SYSDATE-1,'DD')+(0/24) and TRUNC(SYSDATE-1,'DD')+(10.999/24)
    So need the datetime to show the same period.... 00:00 - 10:59

    (I used 10.999/24 to represent 10:59 and it seems to have worked however am curious what the proper SQL notation would be. Using 11/24 gives the extra row with data for 11am but 10.999 supresses the row for 11am)

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    answered in your other thread:
    PHP Code:
    duck@dev01alter session set nls_date_format='mmddyy hh24:mi';

    Session altered.

    duck@dev01select trunc(sysdatebetw1trunc(sysdate)+10/24+59/1440 betw2 
    from dual
    ;

    BETW1        BETW2
    ------------ ------------
    091609 00:00 091609 10:59 
    or use 659/1440:
    PHP Code:
    duck@dev01select trunc(sysdatebetw1trunc(sysdate)+659/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 ...

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.

Posting Permissions

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