|
|
|
|
Generating List of Dates and Times Between Range
To generate list of dates between certain range in Oracle using SQL, you can use query below:
SELECT start_date - LEVEL [+ 1]
FROM DUAL
CONNECT BY LEVEL <= n_of_records_displayed [+ 1];
Query above will return n_of_records_displayed records of date starting from start_date ordered descending. Use [+ 1] if you like to include start_date date.
Below is example query to generate list of dates starting from today backward to the beginning of month:
SELECT SYSDATE - LEVEL + 1 datetime
FROM DUAL
CONNECT BY LEVEL <= (SYSDATE - TRUNC (SYSDATE, 'MONTH')) + 1;
Depend on NLS_LANG parameter setting, query above will approximately return:
DATETIME
--------
08-NOV-12
07-NOV-12
06-NOV-12
05-NOV-12
04-NOV-12
03-NOV-12
02-NOV-12
01-NOV-12
You can always modify query above to return different n_of_records_displayed from different start_date by different interval during different range using different format.
For example, you can list times during 29-NOV-2012 from 0 hour by 1 hour interval with YY/MM/DD-HH24 format:
SELECT TO_CHAR (TO_DATE ('12/10/28-23', 'YY/MM/DD-HH24') +
(LEVEL / 24), 'YY/MM/DD-HH24') datetime
FROM DUAL
CONNECT BY LEVEL <= 24
Regardless to NLS_LANG parameter setting, query above will return:
DATETIME
--------
12/10/29-00
12/10/29-01
12/10/29-02
12/10/29-03
12/10/29-04
12/10/29-05
12/10/29-06
12/10/29-07
12/10/29-08
12/10/29-09
12/10/29-10
12/10/29-11
12/10/29-12
12/10/29-13
12/10/29-14
12/10/29-15
12/10/29-16
12/10/29-17
12/10/29-18
12/10/29-19
12/10/29-20
12/10/29-21
12/10/29-22
12/10/29-23
|
|
|
 |
|
|
|
|
|
|
