CODEWITHSUNDEEP
sqloracle-database
Siva Ponnusamy
Siva Ponnusamy

Reputation: 1

To Show 24 hours in a day

I need a query to show the 24 hours in a current date eventhough data is not present in the database.

For example, SELECT TO_CHAR(yoh.createts, 'YYYY-MM-DD HH24') AS SysHour, it pulls the hour up to current time, I need full 24 hours even data is not in the database.

If I run this query at 1 PM, it pulls the report from 00,01...13 but I need from 00,01,02,03....22,23.

Upvotes: 0

Views: 1740

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

This hierarchical query shows all hours for current date: 

select to_char(trunc(sysdate) + (level-1)/24, 'YYYY-MM-DD HH24') AS SysHour
  from dual connect by level <= 24

SQLFiddle

You'll probably need to left join your existing query to it.

Upvotes: 4

Tenzin
Tenzin

Reputation: 2505

I don't exactly get what you would like to do, but does this give you an idea:
SELECT TO_CHAR(SYSDATE, HH24:MI:SS') FROM dual;

Or the date with time:
SELECT TO_CHAR(SYSDATE, 'YYYY, MONTH DD, HH24:MI:SS') FROM Dual;

Upvotes: 0

Related Questions