date range sql

[rbrooker]

Hi,

found these the other day, thought someone out there could make use of them.

-- this week
SELECT decode(TRIM(to_char(SYSDATE, 'DAY')),
    'SUNDAY', next_day(trunc(SYSDATE - 6), 'SUNDAY'),
    next_day(trunc(SYSDATE - 8), 'SUNDAY')) - 1 + LEVEL
FROM dual
CONNECT BY LEVEL <= 7;

-- last 7 days
SELECT trunc(SYSDATE) - 7 + LEVEL
FROM dual
CONNECT BY LEVEL <= 7;

-- this month
SELECT to_date(to_char(SYSDATE, 'yyyy'), 'yyyy') - 1 + LEVEL
FROM dual
CONNECT BY LEVEL <= to_char(last_day(SYSDATE), 'dd');

-- last 30 days
SELECT trunc(SYSDATE) - 30 + LEVEL dateparam
FROM dual
CONNECT BY LEVEL <= 30;

-- month to date
SELECT to_date(to_char(SYSDATE, 'yyyy'), 'yyyy') - 1 + LEVEL
FROM dual
CONNECT BY LEVEL <= to_char(SYSDATE, 'dd');

-- this year
SELECT to_date('0101' || to_char(SYSDATE, 'yyyy'), 'ddmmyyyy') - 1 + LEVEL
FROM dual
CONNECT BY LEVEL <= 365;

-- last 365 days
SELECT trunc(SYSDATE) - 365 + LEVEL dateparam
FROM dual
CONNECT BY LEVEL <= 365;

-- year to date
SELECT to_date('0101' || to_char(SYSDATE, 'yyyy'), 'ddmmyyyy') - 1 + LEVEL
FROM dual
CONNECT BY LEVEL <= to_char(SYSDATE, 'ddd');

have a nice day

 

[CTzen]

Sweet, thank you

You can actually generate numbers from 1 to N using this query

select  level
  from  dual
connect by level <= N;

 

[ScottMattes]

I don't know if its just my version of Oracle, 9.2, but for the last 3 queries I only get 100 rows returned.

I changed the queries to use rownum vice level, all_objects vice dual and where rownum

 

[adriansi]

select level
from dual
connect by level

 

[CTzen]

>is not working on 8.1.7.4, return 1 row in my 9.2.0.6 instance and N rows in 10gR1

Interesting, it doesn't work in SQL*Plus on 9.2.0.4, but works fine from PL/SQL Developer.

I run PLD with debugsql parameter, but I don't see any 'alter session/system ... ' commands in the output.

 

[Marco van der Linden]

Hide it in an in-line view. E.g.:

select row_number
from   (select level row_number
        from   dual
        connect by level <= :cp_upper_limit
       )

This has to do with dual begin "magic". If SQLPlus sees "dual" it will just return one row.

Check URL below for some interesting reading:

AskTom about "Infinite" dual

 

[CTzen]

Thanks for the link. I'll be getting myself a t-shirt that says

DUAL is Magic