Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: simple time formatting question

    I'm sorry for asking such a simple question, but I've spent a half hour looking in the Oracle documentation and must be missing the key word to search on.

    I need to format/extract an existing date in HH24:MI format.

    I've tried cast and extract but I'm thinking there must be a different way to do this. I'm trying to do something similar to below:

    select extract(hour from sysdate) from dual;
    OR
    select to_timestamp(sysdate,'HH24:MI') from dual;

    What function/command should I be looking for?

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You are looking for to_char():
    select to_char(sysdate, 'HH24:MI') from dual;

    Details are here:
    http://download-west.oracle.com/docs...4a.htm#1009326

  3. #3
    Join Date
    May 2004
    Posts
    184
    Shammat,

    Thank you! My only concern is that the resulting value will also be in an ORDER BY clause. But after doing the test below, it looks like I'll be okay.

    Thank you again for you help.

    Code:
    select * from (
    select '1' as n from dual
    union all
    select '2' as n from dual
    union all
    select '0' as n from dual)
    order by n

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can also use

    select trunc(sysdate,'hh') from dual;

    Which will extract the hour element from sysdate (or any other date).
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2004
    Posts
    184
    Bill,

    Thanks. I'll take a look at that too.

    Robert

Posting Permissions

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