两个日期之间的星期数

本文介绍了两个日期之间的星期数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试获取两个日期之间的星期数.

I'm trying to get the week numbers between two dates.

例如，在2016年1月1日至2016年2月29日之间，我应该拥有

For example, between dates 01/01/2016 and 29/02/2016 I should have

S01 S02 S03 S04 S05 S06 S07 S08

有可能吗?怎么办?

推荐答案

SELECT 'S' || LPAD( LEVEL, 2, '0' ) FROM DUAL CONNECT BY DATE '2016-01-01' + ( LEVEL - 1 ) * 7 <= DATE '2016-02-29';

输出:

'S'||LPAD(LEVEL,2,'0') ---------------------- S01 S02 S03 S04 S05 S06 S07 S08 S09

更新-包含月和日期:

注意:更改界限以突出显示周号和序列号之间的差异.

WITH bounds AS ( SELECT DATE '2016-03-01' AS lower_bound, DATE '2016-04-30' AS upper_bound FROM DUAL ), weeks AS ( SELECT LEVEL AS id, lower_bound + (LEVEL - 1) * 7 AS week_date FROM bounds CONNECT BY lower_bound + (LEVEL - 1) * 7 <= upper_bound ) SELECT 'S' || LPAD( id, 2, '0' ) AS id, 'W' || TO_CHAR( week_date, 'WW' ) AS week, 'M' || TO_CHAR( week_date, 'MM' ) AS month, week_date FROM weeks;

输出:

ID WEEK MONTH WEEK_DATE --- ---- ----- --------- S01 W09 M03 01-MAR-16 S02 W10 M03 08-MAR-16 S03 W11 M03 15-MAR-16 S04 W12 M03 22-MAR-16 S05 W13 M03 29-MAR-16 S06 W14 M04 05-APR-16 S07 W15 M04 12-APR-16 S08 W16 M04 19-APR-16 S09 W17 M04 26-APR-16