ETL跑批日历表提数结束时间功能的实现
来源:互联网 发布:华硕笔记本摄像头软件 编辑:程序博客网 时间:2024/05/21 15:01
实现ETL跑批日历表功能,主要是针对旬报,月报功能,每个月的上中下三旬都需要提供数据,上旬数据11号提供,中旬数据21号提供,下旬数据下一个月的1号提供,其中下旬数据可以在月报数据里面直接根据相关字段得到20号至下个月月初前一天的数据。由于相关跑批程序在传参过程中时间参数只能传入一个(而且传入参数是字符串型的),因此需要通过传进去的时间字符串参数计算所需要的正确提数的结束时间。为此,写了下面的oracle自定义函数FUN_ETL_DATE_TRUN_END.
相关代码如下:
CREATE OR REPLACE FUNCTION FUN_ETL_DATE_TRUN_END (V_ETL_DATE VARCHAR2)RETURN DATEIS/*************************************************************************************** NAME : FUN_ETL_DATE_TRUN_END AUTO : MAOJIANBO DESC : 实现ETL跑批日历表功能,主要是针对旬报,月报功能,每个月的上中下三旬都需要提供数据, 上旬数据11号提供,中旬数据21号提供,下旬数据下一个月的1号提供,其中下旬数据可以在 月报数据里面直接根据相关字段得到20号至下个月月初前一天的数据。由于相关跑批程序在 传参过程中时间参数只能传入一个(而且传入参数是字符串型的),因此需要通过传进去的 时间字符串参数计算所需要的正确提数的结束时间。 INPUT : V_ETL_DATE 传入当前时间字符串,获取所需取数的结束时间END_DATE CALL : SELECT FUN_ETL_DATE_TRUN_END('20160301') FROM DUAL; 2016/2/29 SELECT FUN_ETL_DATE_TRUN_END('20150301') FROM DUAL; 2015/2/28 SELECT FUN_ETL_DATE_TRUN_END('20160801') FROM DUAL; 2016/7/31 DATE : 2016/08/06****************************************************************************************/OUT_RESULT DATE;RESULT VARCHAR2(32):='';BEGIN IF SUBSTR(V_ETL_DATE,5,2) = '02' THEN IF TO_CHAR(LAST_DAY(TO_DATE(V_ETL_DATE,'YYYYMMDD')),'DD')='28' THEN CASE WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='01' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='02' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='03' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='04' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='05' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='06' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='07' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='08' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='09' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='10' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='11' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='12' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='13' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='14' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='15' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='16' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='17' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='18' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='19' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='20' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='21' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='22' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='23' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='24' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='25' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='26' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='27' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='28' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; END CASE; ELSE CASE WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='01' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='02' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='03' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='04' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='05' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='06' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='07' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='08' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='09' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='10' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='11' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='12' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='13' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='14' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='15' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='16' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='17' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='18' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='19' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='20' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='21' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='22' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='23' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='24' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='25' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='26' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='27' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='28' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='29' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; END CASE; END IF; ELSE CASE WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='01' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='02' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='03' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='04' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='05' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='06' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='07' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='08' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='09' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='10' THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD'); WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='11' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='12' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='13' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='14' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='15' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='16' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='17' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='18' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='19' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='20' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='21' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='22' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='23' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='24' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='25' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='26' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='27' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='28' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='29' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='30' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='31' THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20'; END CASE; END IF; OUT_RESULT := TO_DATE(RESULT,'YYYYMMDD'); RETURN OUT_RESULT;END FUN_ETL_DATE_TRUN_END;当然类似的正确的开始取数时间我们也可以根据上面程序来依次类推完成。
0 0
- ETL跑批日历表提数结束时间功能的实现
- JS写的时间日历表
- 输入开始时间和结束时间获取之间相隔的天数,月数,年数
- ogg 使用ETL功能实现数据转换
- 我的日历表
- 简单的日历表写法
- JAVA下的日历表
- MFC下的日历表
- 获取年度周数和每个周的开始时间和结束时间
- 日历表
- 日历表
- 日历表。
- 日历表
- 日历表
- 如何用delphi语言实现结束指定进程的功能
- iOS:CAKeyframeAnimation实现UIImageView精准时间的动画结束事件
- R的Quantmod包ETL功能试用
- 简单好用的时间插件laydate实现开始时间和结束时间的限制
- [洛谷luogu] [NOIP2001T3]统计单词个数 (DP)
- 基于ThinkPHP的二叉树左右值无限极分类实现
- 编译性语言、解释性语言和脚本语言
- Hadoop MapReduce程序分析飞机航班信息源代码
- mybatis高级查询(一对一查询)
- ETL跑批日历表提数结束时间功能的实现
- TCP/IP详解学习笔记
- nodejs连接mysql之使用连接池pool
- oracle11g 完全删除
- 关于FileZilla中进行流量控制的方法
- Qt: qobject_cast<QPushButton*>(sender()) 简化信号与槽的编写
- poj2406(未完成,各种re)
- plsqlDeverloper
- Hihocoder 1356 分隔相同整数 (贪心)