如何在oracle sql中的2个日期之间生成星期日的所有日期? [英] How do I generate all the dates of Sunday between 2 dates in oracle sql?

问题描述

如何在 oracle SQL 中生成两个日期之间 Sunday 的所有日期?

How do I generate all the dates of Sunday between 2 dates in oracle SQL?

例如，如果我想要 "01/10/2018" 和 "31/12/2018" 之间的所有星期天

Example if I want all the Sundays between "01/10/2018" and "31/12/2018" the output

will be:
07/10/2018
14/10/2018
21/10/2018
...
30/12/2018

另外，我如何生成两个日期之间的所有日期?

Also how do I generate all the dates between 2 dates?

示例:从 01/12/2018" 到 31/12/2018"

输出将是:

01/12/2018
02/12/2018
03/12/2018
...
31/12/2018

推荐答案

方法如下；CTE (dates) 创建一个日历"，包含从 2018-10-01 开始的所有日期，用于 2018-10-01 之间的天数code> 和 2018-12-31.这回答了您的第二个问题.

Here's how; the CTE (dates) creates a "calendar" of all dates starting from 2018-10-01, for number of days between 2018-10-01 and 2018-12-31. This answers your 2nd question.

对于第一个问题，使用带有适当格式掩码 (dy) 和日期语言的 TO_CHAR 函数(因为，如果我不使用它，你会得到克罗地亚语名称，因为这是我的默认语言)，请选择所有星期日.

For the 1st question, using TO_CHAR function with appropriate format mask (dy) and date language (because, if I didn't use it, you'd get Croatian names as that's my default language), select all Sundays.

SQL> with dates as
  2    (select date '2018-10-01' + level - 1 datum
  3     from dual
  4     connect by level <= date '2018-12-31' - date '2018-10-01' + 1
  5    )
  6  select datum
  7  From dates
  8  where to_char(datum, 'dy', 'nls_date_language = english') = 'sun';

DATUM
-----------
07-oct-2018
14-oct-2018
21-oct-2018
28-oct-2018
04-nov-2018
11-nov-2018
18-nov-2018
25-nov-2018
02-dec-2018
09-dec-2018
16-dec-2018
23-dec-2018
30-dec-2018

13 rows selected.

SQL>

这篇关于如何在oracle sql中的2个日期之间生成星期日的所有日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！