asked on 4/7/2016

SQL Oracle get a range of dates between two dates

Hello Experts Exchange
I have a SQL script that works in SQL Server, I need a script that works the same in Oracle.

Here is the SQL Server script.

DECLARE @startdate DATETIME= '2016-03-06', @enddate DATETIME= '2016-03-19';
 
WITH calendardates
AS ( 
   SELECT date1 = @startdate
   UNION ALL
   SELECT DATEADD(DAY, 1, date1)
   FROM calendardates
   WHERE DATEADD(DAY, 1, date1) <= @enddate
)
Select *
From calendardates

Open in new window

Results are;

2016-03-06 00:00:00.000
2016-03-07 00:00:00.000
2016-03-08 00:00:00.000
2016-03-09 00:00:00.000
2016-03-10 00:00:00.000
2016-03-11 00:00:00.000
2016-03-12 00:00:00.000
2016-03-13 00:00:00.000
2016-03-14 00:00:00.000
2016-03-15 00:00:00.000
2016-03-16 00:00:00.000
2016-03-17 00:00:00.000
2016-03-18 00:00:00.000
2016-03-19 00:00:00.000

Open in new window

What is the SQL for Oracle to get this to work?

Regards

SQLSearcher

Oracle Database SQL

Last Comment

awking00

4/8/2016

Alex [***Alex140181***]

4/7/2016

select mydate
  from (select trunc(add_months(sysdate, -12), 'yy') - 1 + level as mydate
          from dual
        connect by level <= (select trunc(add_months(sysdate, 24), 'yy') - trunc(add_months(sysdate, -12), 'yy')
                               from dual))
 where mydate >= to_date('06.03.2016', 'dd.mm.yyyy')
   and mydate <= to_date('19.03.2016', 'dd.mm.yyyy');

Open in new window

ASKER CERTIFIED SOLUTION

awking00

4/7/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SQLSearcher

4/8/2016

ASKER

Thank you for your help.

awking00

4/8/2016

Glad to :-)

Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K

Questions

Followers

Top Experts

Get a personalized solution from industry experts

Ask the experts

"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23
"Your help has saved me hundreds of hours of internet surfing."
@fblack61
"Just a dang good service!"
@golferski
"Worth every penny."
Steve Hougom
"It’s been a life saver."
Maria Halt
"Best support site I’ve seen!"
Bob Schneider
"I would be lost without them."
@fblack61
"Saved my job multiple times."
Walt Forbes
"I love this site."
Maria Duwe
"Friendly respectful help."
Andrew Kowtalo
"Such top-notch experts."
@magento
"The best money I have ever spent."
@rwheeler23
"Beyond any comprehensible value"
George Morris
"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23

Read over 600 more reviews

TRUSTED BY