SELECT any FROM system

Question

Can any of these queries be done in SQL?

SELECT dates FROM system 
WHERE dates > 'January 5, 2010' AND dates < 'January 30, 2010'

SELECT number FROM system 
WHERE number > 10 AND number < 20

I'd like to create a generate_series, and that's why I'm asking.

Answer 1

I assume you want to generate a recordset of arbitrary number of values, based on the first and last value in the series.

In PostgreSQL:

SELECT  num
FROM    generate_series (11, 19) num

In SQL Server:

WITH    q (num) AS
        (
        SELECT  11
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num < 19
        )
SELECT  num
FROM    q
OPTION (MAXRECURSION 0)

In Oracle:

SELECT  level + 10 AS num
FROM    dual
CONNECT BY
        level < 10

In MySQL:

Sorry.

Answer 2

Sort of for dates... Michael Valentine Jones from SQL Team has an AWESOME date function

Check it out here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Answer 3

Not sure if this is what you're asking, but if you are wanting to select something not from a table, you can use 'DUAL'

select 1, 2, 3 from dual;

will return a row with 3 columns, contain those three digits.

Selecting from dual is useful for running functions. A function can be run with manual input instead of selecting something else into it. For example:

select some_func('First Parameter', 'Second parameter') from dual;

will return the results of some_func.

Answer 4

In SQL Server you can use the BETWEEN keyword.

Link: http://msdn.microsoft.com/nl-be/library/ms187922(en-us).aspx

Answer 5

You can select a range by using WHERE and AND WHERE. I can't speak to performance, but its possible.

Answer 6

In Oracle

WITH
START_DATE AS
(
    SELECT TO_CHAR(TO_DATE('JANUARY 5 2010','MONTH DD YYYY'),'J') 
    JULIAN FROM DUAL
),
END_DATE AS
(
    SELECT TO_CHAR(TO_DATE('JANUARY 30 2010','MONTH DD YYYY'),'J') 
    JULIAN FROM DUAL
),
DAYS AS
(
    SELECT END_DATE.JULIAN - START_DATE.JULIAN DIFF
    FROM START_DATE, END_DATE
)
SELECT  TO_CHAR(TO_DATE(N + START_DATE.JULIAN, 'J'), 'MONTH DD YYYY') 
        DESIRED_DATES
FROM 
START_DATE,
(
    SELECT LEVEL N 
    FROM DUAL, DAYS
    CONNECT BY LEVEL < DAYS.DIFF
)

Answer 7

If you want to get the list of days, with a SQL like

select ... as days where date is between '2010-01-20' and '2010-01-24'

And return data like:

days 
---------- 
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24 

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last thousand days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.