Displaying each of the 24hrs of the day...

Question

Hi

I was trying to display just the hours in 24hr format like:

select to_char(trunc(sysdate+(1/24)),'HH24:mi') from dual

But this only always returns 00:00. How can I show 01:00 to 23:00?

Thanks and Regards

Answer 1

This will work for Oracle 9i+:

SELECT TO_CHAR(TRUNC(SYSDATE) + (LEVEL / 24)), 'HH24:mi') 
  FROM DUAL
CONNECT BY LEVEL <= 24

Answer 2

If you want to display hours, you need your date to actually contain hours. That means - get your brackets for TRUNC() right.

This is good.

SELECT TO_CHAR(TRUNC(SYSDATE) + (LEVEL / 24), 'HH24:mi') 
  FROM DUAL
CONNECT BY LEVEL <= 24

This is not good.

SELECT TO_CHAR(TRUNC(SYSDATE + (LEVEL / 24)), 'HH24:mi') 
  FROM DUAL
CONNECT BY LEVEL <= 24