Generate list of dates and times


I want to create query returns list of dates and time for example
1-Jan-2012
1-Jan-2012 12:30:00 AM
1-Jan-2012 13:00:00 AM
1-Jan-2012 13:30:00 AM
1-Jan-2012 14:00:00 AM
.................
.................
1-Jan-2012 11:00:00 PM
1-Jan-2012 11:30:00 PM

To execute the previous requirement I can use the below query
   SELECT TO_DATE ('1-1-2012', 'DD-MM-RRRR') + (LEVEL - 1) / 48 DATE_TIME  
    FROM DUAL  
 CONNECT BY LEVEL <= 48;  



The new requirement is I want to make the same list for period of days.
 For example i want to get the dates and times from 1-Jan-2012 to 5-Jan-2012.
To implement the previous requirement I can use the below query

   SELECT TO_DATE ('1-1-2012', 'DD-MM-RRRR') + (LEVEL - 1) / 48 DATE_TIME  
    FROM DUAL  
 CONNECT BY LEVEL <=  
        48  
        * ( TO_DATE ('5-1-2012', 'DD-MM-RRRR')  
          - TO_DATE ('1-1-2012', 'DD-MM-RRRR')  
          + 1);  
 
Conclusion
In previous examples I used operator (48) to represent half hour interval in incrementation, therefore if you want another interval you can use the below equation
1440 /(You Period in minutes)

If you want to get dates and times within specific period you can change dates in second query to your dates.

Thanks

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria