13 November, 2012

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

No comments:

Post a Comment

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...