Re: Is it possible to generate a range of dates by using plain SQL?

From: Matt Brennan <mbrennan_at_gers.antispam.com>
Date: Thu, 18 Jun 1998 22:57:57 GMT
Message-ID: <01bd9b0c$8585e760$049a0580_at_mcb>


This might be a little hoakey, but how about using a sequence number generator that generates a number (don't start it at zero) and you assume that number is a Julian date and do a to_date function on it?:

Get your starting number like this:

select to_num(to_char(to_date('03-JUN-98'),'J'))-1 from dual;

Get your maximum sequence a similar way:

select to_num(to_char(to_date('11-JUN-98'),'J')) from dual;

Then, set up your sequence as incrementing by 1and do this:

insert into date_table
select to_date(to_char(myseq.nextval),'J') from dual; (not sure if the above to_char is necessary or not - maybe you can go from a number directly to a date w/o making the number a character value?)

Run that as often as needed for a new row in your date_table table or use PL/SQL to loop it a certain number of times.

It's kinda bizarre, but might work...?

-- 
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.)

Jack Martens <jack.martens_at_cheerful.com> wrote in article
<6mbt8p$hsu$1_at_thor.wirehub.nl>...

> Is it possible to generate a range of dates by using plain SQL?
>
> Example of the result of the query:
>
> DATE
> ---------
> 03-JUN-98
> 04-JUN-98
> 05-JUN-98
> 06-JUN-98
> 07-JUN-98
> 08-JUN-98
> 09-JUN-98
> 10-JUN-98
> 11-JUN-98
>
> I must be able to select a date from the resulting rows, like this:
>
> SELECT date
> FROM date_table
> where date between '05-JUN-98' and '09-JUN-98'
>
> If it is not possible by using plain SQL, what other things could I try
> (e.g. stored procedures)?
Received on Fri Jun 19 1998 - 00:57:57 CEST

Original text of this message