2024 节假日生成sql脚本 ORACLE

qq_38190509

已于 2024-01-02 16:35:04 修改

阅读量461

点赞数 9

文章标签： sql oracle 数据库

于 2024-01-02 16:30:28 首次发布

本文链接：https://blog.csdn.net/qq_38190509/article/details/135344150

版权

DAY_TYPE ：1工作日，2周末，3节假日

INSERT INTO T_HOLIDAY_CONFIG
(DAY_YMD, DAY_TYPE)
SELECT DAY_YMD,
CASE
WHEN DAY_YMD IN
('2024-01-01', '2024-02-10', '2024-02-11', '2024-02-12',
'2024-02-13', '2024-02-14', '2024-02-15', '2024-02-16',
'2024-02-17', '2024-04-04', '2024-04-05', '2024-04-06',
'2024-05-01', '2024-05-02', '2024-05-03', '2024-05-04',
'2024-05-05', '2024-06-08', '2024-06-09', '2024-06-10',
'2024-09-15', '2024-09-16', '2024-09-17', '2024-10-01',
'2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05',
'2024-10-06', '2024-10-07') THEN
'3'
WHEN DAY_YMD IN ('2024-02-04', '2024-02-18', '2024-04-07',
'2024-04-28','2024-05-11', '2024-09-12', '2024-09-29','2024-10-12') THEN
'1'
ELSE
DAY_TYPE
END DAY_TYPE
FROM (SELECT DAY_YMD,
CASE
WHEN TRIM(TO_CHAR(TO_DATE(DAY_YMD, 'yyyy-MM-dd'), 'day')) IN
('星期六', '星期日') THEN
'2'
ELSE
'1'
END DAY_TYPE
FROM (SELECT TO_CHAR(TO_DATE('2024-01-01', 'yyyy-MM-dd') + ROWNUM - 1,
'yyyy-MM-dd') AS DAY_YMD
FROM DUAL
CONNECT BY ROWNUM <=
TRUNC(TO_DATE('2024-12-31', 'yyyy-MM-dd') -
TO_DATE('2024-01-01', 'yyyy-MM-dd')) + 1));