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
Web Hosting
External links for similar resources:
http://asktom.oracle.com
http://forums.devshed.com
Should you find this any of use, please share:
Facebook LinkedIn Google Twitter
Oracle
:: Januari 2015
:: Februari 2014
:: Maret 2014
:: April 2014
:: Mei 2014
:: Juni 2014
:: Juli 2014
:: Agustus 2014
:: September 2014
:: Oktober 2014
:: November 2014
:: Desember 2014
:: Dinar Emas
:: Dirham Perak
:: Khamsa Perak
:: Lantakan 5g
:: Lantakan 10g
:: Lantakan 25g
:: Lantakan 50g
:: Lantakan 100g
:: Hello Kitty
:: Database Forum
:: Subquery Alias
:: Char Padding
:: UIB
:: JumpShip.Asia
:: Login
Database Forum :: Oracle, PL/SQL, SQL Server, Transact-SQL, T-SQL Tauke Tas :: Tas dan Dompet Wanita Logam Mulia :: Dinar Emas, Dirham Perak, Khamsa Database Forum :: Oracle, PL/SQL, SQL Server, Transact-SQL, T-SQL UIB, Universitas Internasional Batam,  Budi Prayitno Login, Logout
Images contained in this website may be subject to copyright and may not be illegally distributed without proper permission from their each respective owner.
Information contained in this website is for general information purposes only and subject to change without prior notice.
Copyright © 2014 Pernak Pernik™, All Rights Reserved.