Is there any alternative to use MYSQL's ADDDATE() in ORACLE?

Question

I have this query that needs to be executed for oracle sql instead of mysql which is where it originally came from, but I have the ADDDATE() function which I don't see any other alternative than DateAdd since it needs more parameters than I really need..

Apart from that, if I try to execute it, it also indicates an error in the

SELECT 0 i UNION.................

part, saying the following ORA-00923: FROM keyword not found where expected
Maybe in oracle it is not allowed to do a select 0 union select 1 union...
Any suggestions or help I appreciate it, thanks

SELECT 
                    ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
                FROM
                    (
                        SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                    ) t0,
                    (
                        SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                    ) t1,
                    (
                        SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                    ) t2,
                    (
                    SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                    ) t3,
                    (
                    SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                    ) t4

@jarlh Oh right, I hadn't realized that making a dual selection would work... I'll give it a try thanks! — Wail, Feb 09 '23 at 16:03
Please check [Datetime arithmetic](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-E405BBC7-DA9A-4DF2-9F22-E60CB9EC0705) section. You may use `date_variable + n`, where `n` is a number of days or explicit `date_var + interval 'n' days` — astentx, Feb 09 '23 at 16:08
Are you just trying to add a number of days to a DATE or TIMESTAMP? — Pugzly, Feb 09 '23 at 16:41
On a side note: `UNION [DISTINCT]` is inappropriate here. Don't tell the DBMS to look for duplicates to remove, when there can't be any. 0 will never equal 1 or 2 or ... Use `UNION ALL` instead. But well, since MySQL 8 you can write a recursive query instead, anyway. Please see my answer. The query is standard SQL, only that it must be `with dates` in Oracle, but `with recursive dates` in MySQL. — Thorsten Kettner, Feb 09 '23 at 18:26
@Wail it's not *making a dual selection*, it's that in current versions of Oracle, `select` syntax requires a corresponding `from` clause (in accordance with ANSI standards I believe). This may be relaxed in version 23c, but at the time of writing this is not yet released. This is why you can't just `select 0;` and also why Oracle provide a dummy table for cases where there is no real source table. Nobody knows why it's called `dual`, it's just an accident of history. (Actually there is a [story about it](https://stackoverflow.com/a/73816/230471) but tbh it's never really made sense to me.) — William Robertson, Feb 11 '23 at 11:25

score 2 · Answer 1 · answered Feb 09 '23 at 18:16

In Oracle you must select from the one-row table dual in order to select one row. You cannot select without a from clause.

If you want to generate dates, you'll write a standard SQL recursive CTE. (And this is the typical approach now in MySQL, too, since version 8.0.)

Here is an example selecting all days for 1970:

with dates (dt) as
(
  select date '1970-01-01' from dual
  union all
  select dt + interval '1' day from dates where dt < date '1970-12-31'
)
select dt from dates;

score 1 · Answer 2 · answered Feb 09 '23 at 20:35

Here is another way to SELECT a list of dates for the year 1970. Adjust the starting and ending dates if you want different years or the INTERVAL if you want different periods like seconds, minutes, hours…


 ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


with dt (dt, interv) as (
select date '1970-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv <= date '1970-12-31')
select dt from dt;
/

Is there any alternative to use MYSQL's ADDDATE() in ORACLE?

2 Answers2