如何将月份一分为二？即1-15，然后是15——全年的剩余天数是多少？它有点像：
**Data** **Date**
some data 01-01-2019 to 15-01-2019
some data 16-01-2019 to 31-01-2019
some data 01-02-2019 to 15-02-2019
some data 16-02-2019 to 28-02-2019
全年、半年或一年中的季度都是如此？我可以使用DB2吗？
with base (date_value) as (
values ('2020-01-01'),('2020-02-05'),('2020-03-10'),('2020-04-15'),
('2020-05-05'),('2020-06-10'),('2020-07-15'),('2020-08-20'),
('2020-09-11'),('2020-10-16'),('2020-11-21'),('2020-12-26')
)
select
date_value,
CASE
WHEN (day(date_value) <= 15)
THEN CHAR( TO_CHAR(FIRST_DAY(date_value), 'DD-MM-YYYY') || ' to ' || '15-' || LPAD(TO_CHAR(MONTH(date_value)),2, '0') || '-' || TO_CHAR(YEAR(date_value)), 30 )
ELSE
CHAR( '16-' || LPAD(TO_CHAR(MONTH(date_value)),2, '0') || '-' || TO_CHAR(YEAR(date_value)) || ' to ' || TO_CHAR(LAST_DAY(date_value), 'DD-MM-YYYY') , 30)
END BUCKET
from base
最初的部分只是制作一些样本日期。
这将产生以下结果：
DATE_VALUE BUCKET
---------- ------------------------------
2020-01-01 01-01-2020 to 15-01-2020
2020-02-05 01-02-2020 to 15-02-2020
2020-03-10 01-03-2020 to 15-03-2020
2020-04-15 01-04-2020 to 15-04-2020
2020-05-05 01-05-2020 to 15-05-2020
2020-06-10 01-06-2020 to 15-06-2020
2020-07-15 01-07-2020 to 15-07-2020
2020-08-20 16-08-2020 to 31-08-2020
2020-09-11 01-09-2020 to 15-09-2020
2020-10-16 16-10-2020 to 31-10-2020
2020-11-21 16-11-2020 to 30-11-2020
2020-12-26 16-12-2020 to 31-12-2020
12 record(s) selected.