Oracle SQL: Generating Dates in Sequence & Time in Sequence

 To achieve our task we use connect by level in our SQL to generate the followings:

SQL for Generating Dates in Sequence:

Select To_Date(Sysdate-30, 'dd-mm-rrrr') + (Level - 1) Dates
From Dual
Connect By Level <= ((To_Date(Sysdate, 'dd-mm-rrrr') -
To_Date(Sysdate-30, 'dd-mm-rrrr')) + 1);

SQL for Generating Time in Sequence:

Select Level,
To_Char(Trunc(Sysdate) + (Level - 1) / 24, 'YYYY-MM-DD HH24:MI') As Date_Hour,
To_Char(Trunc(Sysdate) + (Level - 1) / 24, 'HH24:MI') As Hours
From Dual
Connect By Level <= 24;

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.

Read More