如何在 Oracle 查询中查找从开始日期到本周结束的周数

Question

我在访问db中有查询，看起来像这样：-

IIf(Weekday([HiredDate])=7,[HiredDate],[HiredDate]-Weekday([HiredDate])) AS TrainingStart, 
Date()+6-Weekday(Date()) AS EndOfWeek, 
DateDiff("ww",[TrainingStart],[EndOfWeek]) AS WeekNumber,

如何在oracle中编写具有相同逻辑的查询。

这是我写的，我认为这是不对的。

SELECT Name, HiredDate, 
(CASE WHEN (to_char(to_date(HiredDate), 'd') = 7) THEN HiredDate ELSE (HiredDate - to_char(to_date(HiredDate), 'd')) END) as TraingStart, 
(CURRENT_DATE + (6 - to_char(to_date(CURRENT_DATE), 'd'))) as EndDate,
(To_Number(to_char(to_date((CURRENT_DATE + (6 - to_char(to_date(CURRENT_DATE), 'd')))),'WW')) - To_number(to_char(to_date((HiredDate - to_char(to_date(HiredDate), 'd'))),'WW')) +1) as WEEKNUMBER 
FROM employee;

Answer 1

我总是使用类似以下内容来计算周数，因为开始和结束日期年份的变化会影响我的结果。

您可以尝试以下操作：

WITH DATE_RANGE AS (
    SELECT
        DATE '2019-12-01' START_DATE,
        DATE '2020-02-26' END_DATE
    FROM
        DUAL
)
SELECT
    COUNT(LEVEL) AS NUMBER_OF_WEEKS
--    , LEVEL   "Week",
--    TRUNC(START_DATE +(7 *(LEVEL - 1)), 'IW') AS START_OF_THE_WEEK,
--    TRUNC(START_DATE +(7 *(LEVEL - 1)), 'IW') + 6 AS END_OF_THE_WEEK,
--    TO_CHAR(START_DATE +(7 *(LEVEL - 1)), 'IW') WEEK_NUMBER
FROM
    DATE_RANGE
CONNECT BY
    LEVEL <= ( TRUNC(END_DATE, 'IW') - TRUNC(START_DATE, 'IW') ) / 7 + 1;
Output:
NUMBER_OF_WEEKS
---------------
             14

干杯！！

Answer 2

试试这个来查找 2 个日期之间的周数。

SELECT (trunc(TO_DATE(CURRENT_DATE, 'DD/MM/YYYY'), 'd')-trunc(TO_DATE(HIRED_DATE, 'DD/MM/YYYY'), 'd'))/7
FROM dual;