Skip to Main Content
  • Questions
  • Generating Dates between two date ranges

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 27, 2003 - 6:48 pm UTC

Answered by: Tom Kyte - Last updated: November 12, 2019 - 4:26 pm UTC

Category: SQL*Plus - Version: 9.0.0

Viewed 100K+ times! This question is

You Asked

Hi Tom ,

I need to write a query which will give me a list of dates between two date ranges (start date and end date) .

For Example if my start date is 12/01/2003 and end Date is 12/31/2003. i need to generate my output as

Date
----
12/01/2003
12/02/2003
12/03/2003
12/04/2003
12/05/2003
12/06/2003
12/07/2003
12/08/2003
.
.
.
so on till
12/31/2003

Can you tell me how to go about generating such a list using simple SQL query.

Thanks in advance
SV

and we said...


ops$tkyte@ORA9IR2> variable start_date varchar2(25)
ops$tkyte@ORA9IR2> variable end_date varchar2(25)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :start_date := '01-dec-2003'; :end_date := '31-dec-2003';

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_date(:start_date,'dd-mon-yyyy') + rownum -1
2 from all_objects
3 where rownum <= to_date(:end_date,'dd-mon-yyyy')-to_date(:start_date,'dd-mon-yyyy')+1
4 /

TO_DATE(:
---------
01-DEC-03
02-DEC-03
03-DEC-03
04-DEC-03
05-DEC-03
06-DEC-03
07-DEC-03
08-DEC-03
09-DEC-03
10-DEC-03
11-DEC-03
12-DEC-03
13-DEC-03
14-DEC-03
15-DEC-03
16-DEC-03
17-DEC-03
18-DEC-03
19-DEC-03
20-DEC-03
21-DEC-03
22-DEC-03
23-DEC-03
24-DEC-03
25-DEC-03
26-DEC-03
27-DEC-03
28-DEC-03
29-DEC-03
30-DEC-03
31-DEC-03

31 rows selected.


and you rated our response

  (43 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thanks

December 28, 2003 - 8:40 am UTC

Reviewer: A reader from New Jersey

Thanks Tom for answering the question . That was a very quick response.

Regards
SV

generating date list...

June 03, 2005 - 4:23 pm UTC

Reviewer: DK

Tom,

I have a table t.

create table t
(start_date date,
interval varchar2(5),
count number);

insert into t (start_date, interval, count)
values (to_date('01/01/2005','MM/DD/YYYY'),'MONTH',3);

insert into t (start_date, interval, count)
values (to_date('01/15/2005','MM/DD/YYYY'),'YEAR',2);

commit;

I want to write a query which will generate dates starting with start_date, at specified interval and count number of times.

So the output from the data in the table should be,

01/01/2005
02/01/2005
03/01/2005
01/15/2005
01/15/2006

first three dates are generated from the first row in t and the next two are generated by the second row in t. How would you go about doing it in oracle8i.

Tom Kyte

Followup  

June 03, 2005 - 5:34 pm UTC

do you have a reasonable upper bound for count.

do you retrieve ALL of the rows from this table or will your application really just get "one of them" and need to explode that out?

Additional Info

June 04, 2005 - 9:08 am UTC

Reviewer: DK

Tom,

Reasonable upper bound for count = 60

Our application will pass a range for start_date or a specific date. That means we need to retrieve a subset of rows from the table.

Thank you

Tom Kyte

Followup  

June 04, 2005 - 11:52 am UTC

ops$tkyte@ORA9IR2> with nums
  2  as
  3  (select level R
  4     from dual
  5  connect by level <= 60
  6  )
  7  select t.start_date,
  8         add_months( start_date, decode(interval,'MONTH',1,'YEAR',12) * (r-1) ) dt,
  9             t.interval,
 10         t.count,
 11             nums.r
 12    from t, nums
 13   where t.count >= nums.r
 14   order by t.start_date
 15  /
 
START_DAT DT        INTER      COUNT          R
--------- --------- ----- ---------- ----------
01-JAN-05 01-JAN-05 MONTH          3          1
01-JAN-05 01-FEB-05 MONTH          3          2
01-JAN-05 01-MAR-05 MONTH          3          3
15-JAN-05 15-JAN-05 YEAR           2          1
15-JAN-05 15-JAN-06 YEAR           2          2


I might advise against using COUNT and INTERVAL as column names as one is a builtin function and the other a datatype. 

solution

June 04, 2005 - 9:18 am UTC

Reviewer: pavan from india

i found this solution.. revert me if any better solution is found

Select Decode(Interval, 'DAY',start_date+indx, 'MONTH',Add_Months(start_date,indx),
to_char(start_date,'dd')||'/'||to_char(start_date,'mm')||'/'||to_char(to_number(to_char(start_date,'yyyy')+indx)))
From
(Select t1.x1 indx, t2.x2 rfrnc
From
(Select rownum x1 From user_objects) t1,
(select rownum x2, t.* from t) t2
Where t1.x1<=t2.COUNT) Main1,
(select rownum x3, t.* From t)main2
where main1.rfrnc = main2.x3



CONNECT BY - Oracle 8i

June 06, 2005 - 8:57 am UTC

Reviewer: DK

Tom,

select level R
from dual
connect by level <= 60

throws ORA-01436:CONNECT BY loop in user data. This is oracle8i database.

Tom Kyte

Followup  

June 06, 2005 - 9:31 am UTC

yes, this works only in 9i and above. No version, I assume software written this century.

You'll replace the reference to dual with any table that as at least 60 rows and use

select rownum r from that_table where rownum <= 60

instead

Connect by "trick"

June 06, 2005 - 10:45 am UTC

Reviewer: Martin from Netherlands

[quote]yes, this works only in 9i and above[/quote]

9IR2 to be exact.

Additional Info

June 06, 2005 - 10:46 am UTC

Reviewer: Mario Jozak from Europe, Croatia, Zagreb,...

Before I didn't know the existance of asktom.com I taught I know pretty much about hierarchy query, and while the time gone, I taught I know more and more. Now, the situation is very diffrent, while I'm reading asktom.com every day I recognize that I know less and less about not only hierarchy querys but everything

Thanks Tom!
You rule!

p.s. you're always blocked with questions (for couple of months), and I would like to ask some. Any tips? (this is kind of a recursion - asking question about asking question :)

Tom Kyte

Followup  

June 06, 2005 - 11:18 am UTC

i can only take about 10 or so new questions a day at most.... it is very much "catch as catch can"

June 07, 2005 - 8:51 am UTC

Reviewer: A reader

Hi Tom,

I want all employees who have joined from last 5 days.
But when there are more than 101 records,below query
is not showing some of the employees who joined today
(i mean that i have added today). I want to always show
all the employees who have joined today and then other days records (chronological order should be from current date to backwards) . How can I do this?
SELECT ENAME,JOB
FROM EMP
WHERE HIREDATE>=5
AND ROWNUM<102;


Between..AND and >= always take first date as starting point.
In my context date2 should be starting point.

SELECT .. FROM TABLE WHERE HIREDATE BETWEEN DATE2 AND DATE1;
(DATE2=JUN 7TH 2005, DATE1 =JUN 2ND 2005)

Please help!
Thanks.

Tom Kyte

Followup  

June 07, 2005 - 8:56 am UTC

select * from emp where hiredate = sysdate
union all
select * from
(select * from emp where hiredate <> sysdate and hiredate >= sysdate-5
order by hiredate desc)
where rownum <= "whatever"
order by hiredate desc

June 07, 2005 - 9:09 am UTC

Reviewer: A reader

Thank you for your quickest reply. But I have one question (for my understanding):
why do we need a subquery in the 2nd half of the query? How is it useful?

Tom Kyte

Followup  

June 07, 2005 - 9:33 am UTC

you had this requirement:

I want to always show
all the employees who have joined today and then other days records
(chronological order should be from current date to backwards)


in order to get the top-n last hired people, we need to sort AND THEN select the top-n

June 07, 2005 - 9:13 am UTC

Reviewer: A reader

And also in the first half we checking if = 'somedate' and so why again say >= 'somedate' in 2nd half? I didn't understand, please explain.

Tom Kyte

Followup  

June 07, 2005 - 9:37 am UTC

you said

a) I want everyone hired today. that is "where hiredate=sysdate"
b) and some of the people hired within the last 5 days -- hence the range.

June 07, 2005 - 9:42 am UTC

Reviewer: A reader

Thanks a lot Tom, it took some time for me to understand.


June 07, 2005 - 10:40 am UTC

Reviewer: A reader

Hi Tom,

Is there a way we can do above task in a single query? instead of using 'union all'; using union / union all will decrease the performance right? Thanks.

Tom Kyte

Followup  

June 07, 2005 - 12:58 pm UTC

union all won't necessarily decrease performance.

You said:

"i want everyone from today"
"and everyone up to a certain limit from the 5 days prior"
"sorted"

that query is likely the best approach given those requirements and UNION ALL isn't "evil"

how would this perform?

June 07, 2005 - 12:17 pm UTC

Reviewer: Alexander from Chicago

I'm wondering if this one will perform well:

select * from emp
--- everybody hired today
where trunc(hiredate) = trunc(sysdate)
-- if less than 100 people hired toady, select some more
or row_number() over(order by hiredate desc)<101
order by hiredate desc

I've heard many times that rownum performs better than row_number(), but haven't seen any good evidence to prove it


Tom Kyte

Followup  

June 07, 2005 - 1:11 pm UTC

two things here. first your query.

I went for "please use indexes to maximum benefit since we are getting so few records". so, I wanted queries to use indexes if possible. trunc(hiredate), don't do that in real life (use between, but yes I was assuming hiredate was *a date*, no time).

the analytics cannot be used in a predicate like that, you'd have to assign row_number() to ALL ROWS IN THE EMP table and then wrap that in an inline view.


I was trying to avoid all of that, get me the first rows as soon as possible.


rownum should be used whenever it can.

select *
from (select * from t order by x)
where rownum < :x;

is much preferred over

select *
from ( select t.*, row_number() over (order by x) rn from t )
where rn < :x
order by x;

the former can do a top-n optimization, the latter cannot -- rownum should be used whenver rownum *can* be used.

More very useful stuff from Ask Tom

February 15, 2006 - 10:24 am UTC

Reviewer: Nicholas Swandel from Ottawa, Canada

Ask Tom is absolutly the best resource at Oracle!

I am always getting great how to anwsers that make me more productive!

I do wish the search indexing was better. Even when I know of an article I am looking for it can be hard to find.

I like the gentle with newbie, modest, and matter of fact tone of the articles.

I just want to say I love you... (singing)

September 13, 2006 - 7:26 am UTC

Reviewer: gold


how about a list of Sundays between two dates

October 27, 2006 - 12:33 pm UTC

Reviewer: jack from Atlanta GA

how would you tweak SQL that gives list of dates to just return list of dates that fall on SUNDAY ( or any other specific weekday ) between two dates.

Tom Kyte

Followup  

October 27, 2006 - 8:05 pm UTC

maybe something like this:

ops$tkyte%ORA10GR2> variable a varchar2(30)
ops$tkyte%ORA10GR2> variable b varchar2(30)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :a := '31-dec-2005'; :b := '25-jul-2006';

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
  2  as
  3  (select level l
  4     from dual
  5  connect by level <= ceil( (to_date(:b,'dd-mon-yyyy')-to_date(:a,'dd-mon-yyyy'))/7 )+1
  6  )
  7  select next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
  8    from data
  9   where next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
 10         between to_date(:a,'dd-mon-yyyy')
 11                 and to_date(:b,'dd-mon-yyyy');

NEXT_DAY(
---------
01-JAN-06
08-JAN-06
15-JAN-06
22-JAN-06
.... 

urgent question

February 14, 2008 - 1:44 am UTC

Reviewer: swadha from india

i have a qustion
i am given start date and end date i need to retrieve those dates that fall in between the start and end date whihc are already stored in the table
e.g.
tid start_date end_date
1 20-feb-08 28-feb-08
2 21-feb-08 27-feb-08
3 23-jan-08 25-jan=08

it should retrieve column having tid 1 and 2 only

kindly answer my request its urgent
Tom Kyte

Followup  

February 14, 2008 - 9:26 am UTC

laughing out loud here.

"it should retrieve column having tid 1 and 2 only"

begs me to ask "why?"

I see some data in a table.
I see no INPUTS to this magic process that knows tid=1,2 are the answer

I also see no create table
no insert into....

nothing to help you with. It is hard to write sql when you don't know what the inputs are, and you have no structures to query.


If your inputs are some date range A and B (such that A <= B)

where end_date >= A and start_date <= B

would seem to do it (simple logic)

            start_date                 end_date

A    B          |                         |                 No (start_date>b)
A               |        B                |                 Yes
A               |                         |   B             Yes
                |        A   B            |                 Yes
                |        A                |   B             Yes
                |                         |   A   B         No (end_date<a)



Your website not readable

February 20, 2008 - 12:56 am UTC

Reviewer: binu from Kuwait


It is vey difficult read something from your websit.You should have to think about changing the content format.I like the queries and answers becuase of its high standard
Tom Kyte

Followup  

February 20, 2008 - 8:20 am UTC

what isn't readable about it?

Get All Sundays

November 03, 2009 - 3:02 pm UTC

Reviewer: Abhijit from Pennsylvania

This code gives me the output below.
/*
              Anonymous block to get all sundays between a given date range
*/
DECLARE
  from_dt DATE := to_date('01/01/2009', 'mm/dd/yyyy');
  to_dt   DATE := to_date('01/29/2009', 'mm/dd/yyyy');
  x       VARCHAR2(30);
BEGIN

  WHILE NOT FROM_DT > TO_DT
  LOOP
    IF to_date(x, 'dd-mon-yyyy') >= to_dt THEN
      dbms_output.put_line('hello');
      EXIT;
    ELSE
      --dbms_output.put_line('FROM DATE ==>' || from_dt);
      x := next_day(from_dt, 'SUN');
      IF to_date(x, 'DD-MON-YYYY') > to_dt THEN
        dbms_output.put_line('Exit Now');
      ELSE
        dbms_output.put_line('Sundays -->' || x);
      END IF;
    END IF;
    from_dt := from_dt + 7;
  END LOOP;
END;


Output:
Sundays -->04-JAN-09
Sundays -->11-JAN-09
Sundays -->18-JAN-09
Sundays -->25-JAN-09
Sundays -->01-FEB-09


What change do I need to make to ensure that 01-Feb-09 does not get printed? Essentially it needs to print all Sundays between the from date and the to date.

Tom Kyte

Followup  

November 09, 2009 - 2:56 pm UTC

ops$tkyte%ORA10GR2> variable x varchar2(30);
ops$tkyte%ORA10GR2> variable y varchar2(30)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :x := '01/01/2009';

PL/SQL procedure successfully completed.


X
--------------------------------
01/01/2009

ops$tkyte%ORA10GR2> exec :y := '01/29/2009';

PL/SQL procedure successfully completed.


Y
--------------------------------
01/29/2009

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
  2  as
  3  (select next_day( to_date(:x,'mm/dd/yyyy')-1, 'Sun' ) + (level-1)*7
  4     from dual
  5  connect by next_day( to_date(:x,'mm/dd/yyyy')-1, 'Sun' ) + (level-1)*7 <= to_date( :y, 'mm/dd/yyyy' ))  6  select * from data
  7  /

NEXT_DAY(TO_DAT
---------------
Sun 04-jan-2009
Sun 11-jan-2009
Sun 18-jan-2009
Sun 25-jan-2009



no code.

November 12, 2009 - 4:01 am UTC

Reviewer: A reader

How can get free time which physician have for any given date range for each parallel schedule id. For example if date given is 11-12-2009 to 11-13-2009 then following data should return

Date Time Parallel_Schedule_Id
11-12-2009 9:26 AM – 9:30 AM 1
11-12-2009 9:46 AM- 1:00 PM 1
11-12-2009 2:00 PM – 3:00 PM 1
11-12-2009 3:31 PM – 6:00 PM 1
11-12-2009 9:21 AM - 12:00 PM 2
11-12-2009 2:00 PM – 6:00 PM 2
11-13-2009 9:00 AM – 01:00 PM 1
11-13-2009 2:00 PM - 6:00 PM 1
11-13-2009 9:00 AM – 12:00 PM 2
11-13-2009 2:00 PM – 6:00 PM 2

CREATE TABLE Physician_Configuration
(
Physician_Configuration_Id NUMBER(8),
Physician_Id NUMBER(4),
From_Time DATE,
To_Time DATE,
Max_Parallel_Schedule NUMBER(2)
);
CREATE TABLE Physician_Configuration_Break
(
Physician_Configuration_Id NUMBER(8),
From_Time DATE,
To_Time DATE,
Parallel_Schedule_Id NUMBER(2)
);

CREATE TABLE Physician_Schedule
(
Physician_Schedule_Id NUMBER(8),
From_Time DATE,
To_Time DATE,
Parallel_Schedule_Id NUMBER(2),
Physician_Id NUMBER(4),
Patient_Id NUMBER(8)
);


SET DEFINE OFF;
Insert into PHYSICIAN_CONFIGURATION
(PHYSICIAN_CONFIGURATION_ID, PHYSICIAN_ID, FROM_TIME, TO_TIME, MAX_PARALLEL_SCHEDULE)
Values
(1, 1, TO_DATE('11/12/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/17/2009 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PHYSICIAN_CONFIGURATION
(PHYSICIAN_CONFIGURATION_ID, PHYSICIAN_ID, FROM_TIME, TO_TIME, MAX_PARALLEL_SCHEDULE)
Values
(2, 1, TO_DATE('12/18/2009 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/29/2010 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
SET DEFINE OFF;
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(1, TO_DATE('11/12/2009 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/17/2009 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(1, TO_DATE('11/12/2009 12:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/17/2009 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(1, TO_DATE('11/12/2009 15:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 16:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into PHYSICIAN_CONFIGURATION_BREAK
(PHYSICIAN_CONFIGURATION_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID)
Values
(2, TO_DATE('12/18/2009 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/29/2010 15:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
SET DEFINE OFF;
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(1, TO_DATE('11/12/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 09:26:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 1,
1);
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(2, TO_DATE('11/12/2009 09:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 09:45:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 1,
2);
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(3, TO_DATE('11/12/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 09:20:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 1,
3);
Insert into PHYSICIAN_SCHEDULE
(PHYSICIAN_SCHEDULE_ID, FROM_TIME, TO_TIME, PARALLEL_SCHEDULE_ID, PHYSICIAN_ID,
PATIENT_ID)
Values
(4, TO_DATE('11/12/2009 15:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2009 15:30:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 1,
4);
COMMIT;



Tom Kyte

Followup  

November 15, 2009 - 1:45 pm UTC

no keys, no sample query to join, nothing from me.

Come on - explain your data - your data is painfully obvious to YOU. To us, it is just a bunch of tables with columns and we don't know how to join.

parallel_schedule_id - a bunch of characters strung together to form words that do not having meaning to us.

November 12, 2009 - 4:02 am UTC

Reviewer: A reader

Version using is 11G release1 ...How can get free time which physician have for any given date range for each parallel schedule id.

November 16, 2009 - 7:30 am UTC

Reviewer: A reader

Sorry for not providing complete information.
Physician_cofiguration table contains configuration for physician for what date to what date he checks patient from what time to what time for example the two records stores in this table are
For date 11/12/2009 to 12/17/2009 he checks patients from time 9:00 AM to 6:00 PM
For date 12/18/2009 to 4/29/2010 he checks patients from time 8:00 AM to 6:00 PM
The MAX_PARALLEL_SCHEDULE column tells how many patients he can schedule for the same time.
PHYSICIAN_CONFIGURATION_BREAK table contains break information for physician which he has set for any date/time and Parallel Schedule configuration he has given in Physician_Configuration. For the example give for first record of Physician_configuration he has set three break intervals First from 1:00 PM to 2:00 PM , Second 3:00 PM to 4:00 PM for Parallel_Schedule_ID 1 and 12:00 PM to 2:00 PM for Pararrel_Schedule_ID 2. Pararel_Schedule_Id are not set in any table these are dependent on Physician_Configuration and shows the Max_Parallel_Schedule no (in case of first record in Physician_Configuration there is max_paralled_schedule is 3 then in physician_cofiguration_break there can be record for parallel_schedule_id 1, 2, 3). PHYSICIAN_SCHEDULE table contains patients scheduled for physician for parallel_schedule_id for specified date and specified time.

November 25, 2009 - 1:02 am UTC

Reviewer: A reader

CREATE TABLE WORK_SHEET
(
WORK_SHEET_ID NUMBER(10),
OPEN_TIME DATE,
CLOSE_TIME DATE,
TIME_LENGTH NUMBER
);

SET DEFINE OFF;
Insert into WORK_SHEET
(WORK_SHEET_ID, OPEN_TIME, CLOSE_TIME, TIME_LENGTH)
Values
(1, TO_DATE('11/24/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/25/2009 18:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
COMMIT;


Open_Time has opening time and date, close_time has closing time and date. time_length has intervals. Output required is

11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm

Means output will be from open_Time to End_Time starting from Date of Open_Time to date of End_time with intervals given in time_length.

Tom Kyte

Followup  

November 25, 2009 - 12:42 pm UTC


ops$tkyte%ORA9IR2> select work_sheet_id,
  2         to_char(open_time + (x.column_value * time_length/24/60),'mm/dd/yyyy hh:mi Am') tim
  3    from work_sheet,
  4         table( cast( multiset( select level-1 r
  5                                      from dual
  6                                                       connect by level <=
  7                                                            ((close_time-open_time) * 24 * 60)/time_length+1 )
  8                                  as sys.odciNumberList ) ) x
  9   where open_time + (x.column_value * time_length/24/60) <= close_time
 10  /

WORK_SHEET_ID TIM
------------- -------------------
            1 11/24/2009 09:00 Am
            1 11/24/2009 09:05 Am
            1 11/24/2009 09:10 Am
...
            1 11/25/2009 05:55 Pm
            1 11/25/2009 06:00 Pm

397 rows selected.


you might need to play with line 7 depending on your time units - these were nicely divisible by 5 (so line 9 was not technically needed) - but it'll depend on the exact nature of your data.

November 26, 2009 - 1:56 am UTC

Reviewer: A reader

Thanks tom but there is one thing for each date the time should be from Start_Time to end_Time and then for next date time will start from Start_Time. as for following data time for 11/24/2009 should be 9:00 am to 6:00 pm and then for date 11/25/2009 it will be again from 9:00 am to 6:00 pm. There should no entries for time onward from end_time

11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm

Tom Kyte

Followup  

November 28, 2009 - 12:42 pm UTC

HUH?


No idea what you mean.

not a clue. You asked a question, I developed a query against your data that gave you precisely the output you described what you wanted.


I don't know what you mean in this followup at all - are you changing your example or output somehow????



my output was:

WORK_SHEET_ID TIM
------------- -------------------
            1 11/24/2009 09:00 Am
            1 11/24/2009 09:05 Am
            1 11/24/2009 09:10 Am
...
            1 11/25/2009 05:55 Pm
            1 11/25/2009 06:00 Pm

397 rows selected.


how does that differ from what you want???!?!?!

December 07, 2009 - 2:02 am UTC

Reviewer: A reader

No i am not changing example or output, if you see my question i asked output as follows:

11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm

The output is for 11/24/2009 from 9:00 am to 6:00 Pm and then starts 11/25/2009 from 9:00 am to 6:00 pm. Sorry i could not make it clear in question but the output was showing it.

Tom Kyte

Followup  

December 07, 2009 - 2:53 pm UTC

I then have no clue what you are asking for. As far as I can tell you asked:


Output required is

11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm

Means output will be from open_Time to End_Time starting from Date of Open_Time to date of End_time
with intervals given in time_length.


and I responded:


ops$tkyte%ORA9IR2> select work_sheet_id,
2 to_char(open_time + (x.column_value * time_length/24/60),'mm/dd/yyyy hh:mi Am') tim
3 from work_sheet,
4 table( cast( multiset( select level-1 r
5 from dual
6 connect by level <=
7 ((close_time-open_time) * 24 *
60)/time_length+1 )
8 as sys.odciNumberList ) ) x
9 where open_time + (x.column_value * time_length/24/60) <= close_time
10 /

WORK_SHEET_ID TIM
------------- -------------------
1 11/24/2009 09:00 Am
1 11/24/2009 09:05 Am
1 11/24/2009 09:10 Am
...
1 11/25/2009 05:55 Pm
1 11/25/2009 06:00 Pm

397 rows selected.



WHICH IS EXACTLY WHAT YOU ASKED FOR - exactly.


but you wrote:


There should no entries for time onward from end_time

11/24/2009 9:00 Am
11/24/2009 9:05 Am
11/24/2009 9:10 Am
..
..
11/24/2009 6:00 Pm
11/25/2009 9:00 Am
11/25/2009 9:05 Am
11/25/2009 9:10 Am
..
..
11/25/2009 6:00 Pm




and I have no idea what you mean.

December 08, 2009 - 1:32 am UTC

Reviewer: A reader

Thanks for your patience and time.

By saying "there should no entries for time onward from end_time" i ment that for each date time should start from start time (9:00 Am) and end time should be (6:00 Pm) then there should no entries for 6:05 pm to 8:55 am. Next date will start with 9:00 am to 6:00 pm. I wanted that for each date duration should be between start_time to end_time and there should no records for end_time of current date.
Tom Kyte

Followup  

December 10, 2009 - 8:21 am UTC

I'd say "your data model is whacked, broken, wrong"

however, you cannot figure out how to correct the query output with a simple where clause? Are you sure you cannot?


we have

9 where open_time + (x.column_value * time_length/24/60) <= close_time

what if you added to it.....


and to_char(open_time + (x.column_value * time_length/24/60),'hh24mi')
between '0900' and '1800'

ops$tkyte%ORA9IR2> select work_sheet_id,
  2         to_char(open_time + (x.column_value * time_length/24/60),'mm/dd/yyyy hh:mi Am') tim
  3    from work_sheet,
  4         table( cast( multiset( select level-1 r
  5                                      from dual
  6                                                       connect by level <=
  7                                                            ((close_time-open_time) * 24 * 60)/time_length+1 )
  8                                  as sys.odciNumberList ) ) x
  9   where open_time + (x.column_value * time_length/24/60) <= close_time
 10     and to_char(open_time + (x.column_value * time_length/24/60),'hh24mi') between '0900' and '1800'
 11  /

WORK_SHEET_ID TIM
------------- -------------------
            1 11/24/2009 09:00 Am
            1 11/24/2009 09:05 Am
...
            1 11/24/2009 05:50 Pm
            1 11/24/2009 05:55 Pm
            1 11/24/2009 06:00 Pm
            1 11/25/2009 09:00 Am
            1 11/25/2009 09:05 Am
...
            1 11/25/2009 05:55 Pm
            1 11/25/2009 06:00 Pm

218 rows selected.

To Reader above

December 09, 2009 - 4:31 am UTC

Reviewer: Raj from IN

How about a query like this?

scott@orcl> variable sdate varchar2(20)
scott@orcl> variable edate varchar2(20)
scott@orcl> variable mint number
scott@orcl> exec :sdate := '01-Dec-2009 09:00:00';

PL/SQL procedure successfully completed.

scott@orcl> exec :edate := '02-Dec-2009 18:00:00';

PL/SQL procedure successfully completed.

scott@orcl> exec :mint := 5

PL/SQL procedure successfully completed.

scott@orcl>

scott@orcl> with data as (select to_date(:sdate,'dd-Mon-yyyy hh24:mi:ss') sdate,

2 to_date(:edate,'dd-Mon-yyyy hh24:mi:ss') edate, :mint mint from dual
3 )
4 select * from (
5 select sdate + ((level-1)*mint/24/60) sdate, edate from data
6 connect by level <= (edate - sdate)*24*60 )
7 where edate >= sdate
8 and to_number(to_char(sdate,'hh24mi')) between 900 and 1800
9 /

SDATE EDATE
-------------------- --------------------
01-Dec-2009 09:00:00 02-Dec-2009 18:00:00
01-Dec-2009 09:05:00 02-Dec-2009 18:00:00
01-Dec-2009 09:10:00 02-Dec-2009 18:00:00
01-Dec-2009 09:15:00 02-Dec-2009 18:00:00
01-Dec-2009 09:20:00 02-Dec-2009 18:00:00
01-Dec-2009 09:25:00 02-Dec-2009 18:00:00
........
01-Dec-2009 17:50:00 02-Dec-2009 18:00:00
01-Dec-2009 17:55:00 02-Dec-2009 18:00:00
01-Dec-2009 18:00:00 02-Dec-2009 18:00:00
02-Dec-2009 09:00:00 02-Dec-2009 18:00:00
02-Dec-2009 09:05:00 02-Dec-2009 18:00:00
.......
02-Dec-2009 11:40:00 02-Dec-2009 18:00:00
02-Dec-2009 11:45:00 02-Dec-2009 18:00:00
.......
02-Dec-2009 17:55:00 02-Dec-2009 18:00:00
02-Dec-2009 18:00:00 02-Dec-2009 18:00:00

218 rows selected.

scott@orcl>

Now I may have missed some boundary condition checking, may be Tom can point out those.

December 10, 2009 - 8:38 am UTC

Reviewer: A reader

thanks. I'd say "your data model is whacked, broken, wrong" how can be model managed properly.

We need to keep date range and also start and end time. What you suggest keep separate fields for start date , end date and start time and end time. currently open_time and close_time have both time and date.
CREATE TABLE WORK_SHEET
(
WORK_SHEET_ID NUMBER(10),
OPEN_TIME DATE,
CLOSE_TIME DATE,
TIME_LENGTH NUMBER
);

Tom Kyte

Followup  

December 10, 2009 - 3:17 pm UTC

if you wanted non-contiguous things like that, it would call for separate attributes typically


date range
start/stop times for the date range.

what you have works, but is not intuitive or clear.

Another Approach

March 05, 2010 - 5:52 pm UTC

Reviewer: Robert Frick from Atlanta GA.

Developed this to generate a list of descending dates for the next two years. I got on this path thanks to the previous entries. Thanks !
select
to_char(sysdate - level,'Mon'),
to_char(sysdate - level,'YYYY')
from dual connect by level <= 730
group by to_char(sysdate - level,'YYYYMM'),
to_char(sysdate - level,'Mon'),
to_char(sysdate - level,'YYYY')
order by to_char(sysdate - level,'YYYYMM') desc

Tom Kyte

Followup  

March 09, 2010 - 10:33 am UTC

ops$tkyte%ORA10GR2> select to_char( add_months(trunc(sysdate,'mm'),level-1), 'Mon YYYY')
  2  from dual connect by level <= 25
  3  order by add_months(trunc(sysdate,'mm'),level-1)
  4  /
TO_CHAR(
--------
Mar 2010
Apr 2010
May 2010
Jun 2010
Jul 2010
Aug 2010
Sep 2010
Oct 2010
Nov 2010
Dec 2010
Jan 2011
Feb 2011
Mar 2011
Apr 2011
May 2011
Jun 2011
Jul 2011
Aug 2011
Sep 2011
Oct 2011
Nov 2011
Dec 2011
Jan 2012
Feb 2012
Mar 2012

25 rows selected.



might be better.

Generating dates between two date ranges

April 01, 2010 - 1:17 pm UTC

Reviewer: Ron F from Seattle, WA

Thought that this might also be a terser way to get the same results for Sundays between a range, based on Oct , 2006 post

select to_date ('2005-12-30', 'yyyy-mm-dd') + (level - 1) as desired_date
from dual
where to_char(to_date('2005-12-30', 'yyyy-mm-dd') + level - 1, 'DY') = 'SUN'
connect by level <= ceil( (to_date('25-jul-2006','dd-mon-yyyy')
-to_date('30-dec-2005','dd-mon-yyyy'))+1 )
order by desired_date ;

DESIRED_DATE
1/1/2006
1/8/2006
1/15/2006
1/22/2006
1/29/2006
...

Business Days between two dates

April 07, 2010 - 5:27 pm UTC

Reviewer: Ashok from Chicago, IL USA

Hi Tom,
Please help me in getting the number of business day between two dates(DATE_REQ and DATE_COM ) in a table WRK.(Business day = Excluding Saturday, Sunday and holidays). Holidays are in separte table(Holiday).

(E.g)
CREATE TABLE WRK (ID VARCHAR2(10),DATE_REQ DATE, DATE_COM DATE);

INSERT INTO WRK VALUES(1,TO_DATE('01/18/2010','MM/DD/YYYY'),TO_DATE('01/23/2010','MM/DD/YYYY'));

INSERT INTO WRK VALUES(2,TO_DATE('02/15/2010','MM/DD/YYYY'),TO_DATE('02/19/2010','MM/DD/YYYY'));

INSERT INTO WRK VALUES(3,TO_DATE('02/22/2010','MM/DD/YYYY'),TO_DATE('02/24/2010','MM/DD/YYYY'));

CREATE TABLE Holiday (HOL_DATE DATE,DESCRIPTION VARCHAR2(20));

INSERT INTO HOLIDAY('01/21/2010','Martin Luther King, Jr');


INSERT INTO HOLIDAY VALUES('01/21/2010,'Martin Luther King, Jr');

INSERT INTO HOLIDAY('02/18/2010','Presidents Day');

Thanks,
Kumar
Tom Kyte

Followup  

April 13, 2010 - 7:42 am UTC

http://asktom.oracle.com/pls/asktom/asktom.search?p_string=number+of+business+days

might return an interesting hit or two?

How about a list of Sundays and Mondays Between Two Dates?

May 19, 2010 - 3:14 pm UTC

Reviewer: Salan from Bahrain

I already saw the below follow up post on how to list all dates that fall on Sundays between two dates, but how we could add also to the same list all dates that fall on Mondays as well?


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data
2 as
3 (select level l
4 from dual
5 connect by level <= ceil( (to_date(:b,'dd-mon-yyyy')-to_date(:a,'dd-mon-yyyy'))/7 )+1
6 )
7 select next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
8 from data
9 where next_day( to_date(:a,'dd-mon-yyyy')-14, 'sun')+l*7
10 between to_date(:a,'dd-mon-yyyy')
11 and to_date(:b,'dd-mon-yyyy');

NEXT_DAY(
---------
01-JAN-06
08-JAN-06
15-JAN-06
22-JAN-06
....
Tom Kyte

Followup  

May 24, 2010 - 11:24 am UTC

modify the where clause - look at the where clause and change it to keep only mondays.

date range

June 02, 2011 - 9:46 am UTC

Reviewer: A reader

CREATE TABLE TEST_DATE
(
ID NUMBER(2),
FROM_DATE DATE,
TO_DATE DATE
);

SET DEFINE OFF;
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(1, TO_DATE('04/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(2, TO_DATE('04/12/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/15/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(3, TO_DATE('06/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATE
(ID, FROM_DATE, TO_DATE)
Values
(4, TO_DATE('10/20/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
There are missing dates, need to fill these gaps for example (5/16/2010 to 6/09/2010, 9/26/2010 to 10/19/2010)
Tom Kyte

Followup  

June 02, 2011 - 10:17 am UTC

ops$tkyte%ORA11GR2> with data as (select 1 r from dual union all select 2 r from dual)
  2  select id,
  3         case when r = 1 then from_date else to_date+1 end f_date,
  4             case when r = 1 then to_date else next_date-1 end t_date,
  5             case when r = 2 then '*' end flag
  6    from (
  7  select id, from_date, to_date, next_date, r
  8    from (
  9  select id, from_date, to_date, lead(from_date) over (order by id) next_date
 10    from test_date
 11         ) x, data
 12   where r = 1
 13      or (r=2 and next_date <> to_date+1 )
 14             )
 15   order by id, r
 16  /

        ID F_DATE    T_DATE    F
---------- --------- --------- -
         1 01-APR-10 11-APR-10
         2 12-APR-10 15-MAY-10
         2 16-MAY-10 09-JUN-10 *
         3 10-JUN-10 25-SEP-10
         3 26-SEP-10 19-OCT-10 *
         4 20-OCT-10 01-NOV-10

6 rows selected.


presuming no overlaps in the existing data - just missing gaps...

date

September 13, 2011 - 6:12 am UTC

Reviewer: sruthi from us

hi tom,
i want to retrieve data between to dates irrepctible of year
Tom Kyte

Followup  

September 13, 2011 - 6:27 am UTC

where to_number( to_char( date_column, 'mmdd' ) ) between to_number( to_char( :first_date, 'mmdd' ) ) and to_number( to_char( :second_date, 'mmdd' ) )

would be one approach.

November 24, 2012 - 3:13 am UTC

Reviewer: rebel

Hi Tom,

I have a table in below format,

startdate endate bednumber
---------- ------- ---------
24/11/2012 26/11/2012 XYZ

I need the following result to be inserted in a table.

24-Nov XYZ
25-Nov XYZ
26-Nov XYZ


Thanks in advance


Tom Kyte

Followup  

November 29, 2012 - 7:44 am UTC

here is a query that works if you just have one row to target by some key, then:


ops$tkyte%ORA11GR2> create table t
  2  as
  3  select 1 id, trunc(sysdate-5) startdate, trunc(sysdate-2) enddate, 'xyz' bednumber from dual
  4  union all
  5  select 2 id, trunc(sysdate-7) startdate, trunc(sysdate-3) enddate, 'abc' bednumber from dual
  6  /

Table created.



ops$tkyte%ORA11GR2> select enddate-startdate+1 ndates,
  2         startdate,
  3         enddate,
  4         startdate+rownum-1 thedate,
  5         bednumber
  6    from t
  7   start with id = 1
  8  connect by level <= enddate-startdate+1 and id=1
  9  /

    NDATES STARTDATE ENDDATE   THEDATE   BED
---------- --------- --------- --------- ---
         4 24-NOV-12 27-NOV-12 24-NOV-12 xyz
         4 24-NOV-12 27-NOV-12 25-NOV-12 xyz
         4 24-NOV-12 27-NOV-12 26-NOV-12 xyz
         4 24-NOV-12 27-NOV-12 27-NOV-12 xyz


this works for any number of rows - from zero to whatever:
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select id, startdate, enddate,
  2         startdate+COLUMN_VALUE-1 thedate,
  3         bednumber
  4    from t, TABLE(
  5              cast(
  6                multiset( select level l
  7                            from dual
  8                         connect by level <= enddate-startdate+1) as sys.odciNumberList )
  9                 )
 10   where id = 2
 11  /

        ID STARTDATE ENDDATE   THEDATE   BED
---------- --------- --------- --------- ---
         2 22-NOV-12 26-NOV-12 22-NOV-12 abc
         2 22-NOV-12 26-NOV-12 23-NOV-12 abc
         2 22-NOV-12 26-NOV-12 24-NOV-12 abc
         2 22-NOV-12 26-NOV-12 25-NOV-12 abc
         2 22-NOV-12 26-NOV-12 26-NOV-12 abc

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select id, startdate, enddate,
  2         startdate+COLUMN_VALUE-1 thedate,
  3         bednumber
  4    from t, TABLE(
  5              cast(
  6                multiset( select level l
  7                            from dual
  8                         connect by level <= enddate-startdate+1) as sys.odciNumberList )
  9                 )
 10  /

        ID STARTDATE ENDDATE   THEDATE   BED
---------- --------- --------- --------- ---
         1 24-NOV-12 27-NOV-12 24-NOV-12 xyz
         1 24-NOV-12 27-NOV-12 25-NOV-12 xyz
         1 24-NOV-12 27-NOV-12 26-NOV-12 xyz
         1 24-NOV-12 27-NOV-12 27-NOV-12 xyz
         2 22-NOV-12 26-NOV-12 22-NOV-12 abc
         2 22-NOV-12 26-NOV-12 23-NOV-12 abc
         2 22-NOV-12 26-NOV-12 24-NOV-12 abc
         2 22-NOV-12 26-NOV-12 25-NOV-12 abc
         2 22-NOV-12 26-NOV-12 26-NOV-12 abc

9 rows selected.

to date and next from date is greater than 60 Days

November 20, 2013 - 7:24 am UTC

Reviewer: chandu from india

Hi Tom,

For same member withy different lines to date and end date for all lines total should be greater than 100 days. If below 100 days we dont want all above lines and The difference between to date and next from date is greater than 60 Days so from here new count of 100 days starts again.

Greg

July 01, 2014 - 9:03 am UTC

Reviewer: Greg from South Africa

Most helpful

July 23, 2014 - 1:14 pm UTC

Reviewer: A reader

Thanks for giving such answer...

very much useful

June 28, 2016 - 12:40 pm UTC

Reviewer: Amar from India

How can I get dates alternately in two columns

like

01-03-2016 02-03-2016
03-03-2016 04-03-2016
.
.
.
.
like that


Thank You..
Connor McDonald

Followup  

June 29, 2016 - 3:35 am UTC

SQL> select
  2    date '2010-01-01' + 2*rownum-2 x,
  3    date '2010-01-01' + 2*rownum-1 y
  4  from dual
  5  connect by level <= 10;

X         Y
--------- ---------
01-JAN-10 02-JAN-10
03-JAN-10 04-JAN-10
05-JAN-10 06-JAN-10
07-JAN-10 08-JAN-10
09-JAN-10 10-JAN-10
11-JAN-10 12-JAN-10
13-JAN-10 14-JAN-10
15-JAN-10 16-JAN-10
17-JAN-10 18-JAN-10
19-JAN-10 20-JAN-10

10 rows selected.


Thank you very much sir

June 29, 2016 - 4:34 am UTC

Reviewer: Amar from india

thanks for the valuable time
Connor McDonald

Followup  

June 29, 2016 - 6:53 am UTC

glad we could help

June 29, 2016 - 4:41 am UTC

Reviewer: Amar from india

There are two input dates and I want to display
dates between these two input dates like,

Start Date :- 01-04-2016
End Date :- 01-05-2016

01-04-2016 02-04-2016
03-04-2016 04-04-2016
.
.
.
.

Connor McDonald

Followup  

June 29, 2016 - 6:58 am UTC

You just need to have the right number of rows...eg

SQL> select
  2    x,
  3    case when y <= date '2010-01-31' then y end y
  4  from (
  5  select
  6        date '2010-01-01' + 2*rownum-2 x,
  7        date '2010-01-01' + 2*rownum-1 y
  8  from dual
  9  connect by level <= ( date '2010-01-31' - date '2010-01-01' ) / 2 + 1
 10  );

X         Y
--------- ---------
01-JAN-10 02-JAN-10
03-JAN-10 04-JAN-10
05-JAN-10 06-JAN-10
07-JAN-10 08-JAN-10
09-JAN-10 10-JAN-10
11-JAN-10 12-JAN-10
13-JAN-10 14-JAN-10
15-JAN-10 16-JAN-10
17-JAN-10 18-JAN-10
19-JAN-10 20-JAN-10
21-JAN-10 22-JAN-10
23-JAN-10 24-JAN-10
25-JAN-10 26-JAN-10
27-JAN-10 28-JAN-10
29-JAN-10 30-JAN-10
31-JAN-10


want to build a date wise table

August 31, 2019 - 7:48 am UTC

Reviewer: A reader

eg. I have a table like this,
Dat acc_balance
1-sep-2019 1000
5-sep-2019 2000
6-sep-2019 2500
9-sep-2019 3000

table to be build like below
1-sep-2019 1000
2-sep-2019 1000
3-sep-2019 1000
4-sep-2019 1000
5-sep-2019 2000
6-sep-2019 2500
7-sep-2019 2500
8-sep-2019 2500
9-sep-2019 3000

Chris Saxon

Followup  

September 02, 2019 - 10:17 am UTC

You can use lead to convert the data to a series of start/end dates
with dates as (
  select date'2019-09-01' dt from dual union all 
  select date'2019-09-03' dt from dual union all 
  select date'2019-09-07' dt from dual
)
  select dt start_dt, 
         lead ( dt ) over ( 
           order by dt 
         ) end_date 
  from   dates;

START_DT                END_DATE               
01-SEP-2019 00:00:00    03-SEP-2019 00:00:00    
03-SEP-2019 00:00:00    07-SEP-2019 00:00:00    
07-SEP-2019 00:00:00    <null>   


From here it's just a matter of joining this to a table of all the dates themselves. Which could be a physical table or generated as above.

slight performance hit for JDBC clients

November 12, 2019 - 3:47 pm UTC

Reviewer: John from Grand Rapids, MI USA

Very helpful even so many years later.

Does it make sense that this query would run 100's of milliseconds longer via JDBC client than a "traditional" oracle client?

If I replace all_objects with any old table the user can query, the performance difference goes away, FYI. Perhaps a JDBC client doesn't cache all_objects.

Chris Saxon

Followup  

November 12, 2019 - 4:26 pm UTC

Perhaps a JDBC client doesn't cache all_objects

The driver won't cache the table. It's the database that does that.

Is this repeatable? Are you using the same user for JDBC and Oracle client tests? Have you got an example code you can share?