您可以在Oracle和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;

但是如果你可以使用不同的查询，最好使用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在H2的未来版本中，将可能使用更简单的查询。

我如何重写查询，使它与Oracle和H2一起工作？
比较Oracle和H2 functions：

• Oracle支持TRUNC，H2支持DATE_TRUNC
• Oracle支持LAST_DAY，而H2不支持。

H2似乎支持函数EXTRACT和CAST，如果它支持递归查询，那么你可以只使用这些函数编写一些东西（但这并不好）：

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;

或者，如果H2不支持将数字添加到日期中，则可以使用INTERVAL s：

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;

这两个输出：
| DAY |
| ------------ |
| 2023-06-01 00:00:00 |
| 2023-06-02 00:00:00 |
| 2023-06-03 00:00:00 |
| ... |
| 2023-06-29 00:00:00 |
| 2023-06-30 00:00:00 |
我没有访问H2示例的权限来测试它是否在RDBMS上工作。
但是，您可能会发现为每个数据库编写不同的代码比尝试重用相同的代码更简单。
fiddle