Translate Oracle query to H2

I have the following query, which works fine in Oracle:

SELECT TRUNC(CURRENT_DATE, 'MM') + LEVEL - 1 AS MYDATE FROM DUAL CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(CURRENT_DATE));

This query returns a list with all the days from current month.

The issue is that I need this query to also work in H2, but H2 does not support LEVEL or TRUNC or CONNECT BY. Therefore, how could I rewrite the query so that it works with both Oracle and H2?

You can use the following query with both Oracle and H2:

WITH T(MYDATE) AS (
SELECT CAST(TRUNC(CURRENT_DATE)
 - INTERVAL '1' DAY * (EXTRACT(DAY FROM CURRENT_DATE) - 1) AS DATE) MYDATE
FROM DUAL
UNION ALL
SELECT CAST(MYDATE AS DATE) + INTERVAL '1' DAY
FROM T
WHERE EXTRACT(MONTH FROM CAST(MYDATE AS DATE) + INTERVAL '1' DAY)
  = EXTRACT(MONTH FROM CURRENT_DATE)
)
SELECT MYDATE FROM T;

But if you can use different queries, it will be better to use that one with H2:

SELECT DATE_TRUNC(MONTH, CURRENT_DATE) + INTERVAL '1' DAY * X MYDATE
FROM SYSTEM_RANGE(0, EXTRACT(DAY FROM
  DATE_TRUNC(MONTH, CURRENT_DATE) + INTERVAL '1' MONTH - INTERVAL '2' DAY));

(In future versions of H2 it will be possible to use a simpler query.)

> how could I rewrite the query so that it works with both Oracle and H2?

Not easily. Comparing Oracle and H2 functions:

• Oracle supports TRUNC and H2 supports DATE_TRUNC
• Oracle supports LAST_DAY and H2 does not.

H2 appears to support the functions EXTRACT and CAST and if it supports recursive queries then you could write something using only those functions (but it is not nice):

WITH days (day, month) AS (
  SELECT CURRENT_DATE
         - EXTRACT(DAY FROM CURRENT_DATE) + 1
         - EXTRACT(HOUR FROM CAST(CURRENT_TIMESTAMP AS TIMESTAMP)) / 24
         - EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) / 24 / 60
         - FLOOR(EXTRACT(SECOND FROM CURRENT_TIMESTAMP)) / 24 / 60 / 60,
         EXTRACT(MONTH FROM CURRENT_DATE)
  FROM   DUAL
UNION ALL
  SELECT day + 1,
         month
  FROM   days
  WHERE  EXTRACT(MONTH FROM day + 1) = month
)
SELECT day
FROM   days;

or, if H2 does not support adding numbers to dates then you can use INTERVALs:

WITH days (day, month) AS (
  SELECT CURRENT_DATE
         - INTERVAL '1' DAY * (EXTRACT(DAY FROM CURRENT_DATE) - 1)
         - INTERVAL '1' HOUR * EXTRACT(HOUR FROM CAST(CURRENT_TIMESTAMP AS TIMESTAMP))
         - INTERVAL '1' MINUTE * EXTRACT(MINUTE FROM CURRENT_TIMESTAMP)
         - INTERVAL '1' SECOND * FLOOR(EXTRACT(SECOND FROM CURRENT_TIMESTAMP)),
         EXTRACT(MONTH FROM CURRENT_DATE)
  FROM   DUAL
UNION ALL
  SELECT day + INTERVAL '1' DAY,
         month
  FROM   days
  WHERE  EXTRACT(MONTH FROM day + INTERVAL '1' DAY) = month
)
SELECT day
FROM   days;

Which both output:

I don't have access to a H2 instance to test whether it works on that RDBMS.

However, you may find it simpler to write different code for each database rather than trying to re-use the same code.

fiddle