Re: Oracle Date SQL challenge

From: <mujeeb_ur_rehman_at_hotmail.com>
Date: Thu, 13 Aug 1998 13:35:23 GMT
Message-ID: <6quq2r$mvr$1@nnrp1.dejanews.com>

Hi :)

Here is the simple solution with sql you searching for. Just cut and paste between lines and do the needful modification and u are set to go.

NOTE : Replace 'all_objects' with the table or view which has enough rows to meet the difference of max - min.

Hope it helps.

---

set verify off
column mindate noprint new_value xmin
column maxdate noprint new_value xmax
column req_result format a12

select min(min_date_column_in_your_table) mindate,

       max(max_date_column_in_your_table) maxdate from your_table_name
/

select to_char(to_date('&xmin','DD-MON-YY')+rownum-1,

       'DD-MON-YYYY') Req_Result
from all_objects
where rownum <= (to_date('&xmax','DD-MON-YY') -

                 to_date('&xmin','DD-MON-YY')) + 1

/
clear columns
set verify on
undefine xmin
undefine xmax

---

In article <6qq97l$gdo$1_at_Starbase.NeoSoft.COM>,   allenh_at_Starbase.NeoSoft.COM (Allen) wrote:
>
> Here is the problem:
>
> given a min and a max date, create a SQL statement to return a date
> for each day between min_date and max_date (ie, for use in the
> creation of a view).
>
> min(start_date) and max(start_date) are in a table, and I want to get
> a list of all days between them (but not all are in that table, since
> it does not include Sat, Sun, and Holidays).
>
> This gets tricky - If I have just 2 days: 1-jan and 10-jan, I still
> want to return 10 rows, 1 for each date.
>
> Any ideas? Yes, I could always write a PL/SQL procedure to do this,
> but I was wondering if there was a simple SQL statement that might
> also work. Thx..
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 13 1998 - 08:35:23 CDT