CREATE OR REPLACE Package Pkg_Stm_Date As
--Purpose:相关日期处理功能包
--获取某一天是第几周
Function Fn_GetWeekbyDate(P_Date Varchar2) Return Varchar2;
--获取某一天是第几周
Function Fn_GetWeekbyDate(P_Date Date) Return Varchar2;
--获取某一天是周几
Function Fn_GetWeekDaybyDate(P_Date Varchar2) Return Varchar2;
--获取某一天是周几
Function Fn_GetWeekDaybyDate(P_Date Date) Return Varchar2;
--获取该日期所在的周是单周还是双周
Function Fn_GetWeekTypeByDate(P_Date Varchar2) Return Pls_Integer;
--根据查询日期,获取该日期所在的星期的星期一对应的日期
Function Fn_GetMonDayByDate(P_Date Varchar2) Return Varchar2;
--获取连续的日期列表,返回游标类型
Function Fn_GetDateList(P_StartDate Varchar2, P_EndDate Varchar2) Return Sys_refcursor;
--获取连续的日期列表,返回结果集
Function Fn_GetDateTable(P_StartDate Varchar2, P_EndDate Varchar2) Return TblTyp_Var Pipelined;
--获取当天日期
Function Fn_GetCurrentDate Return Varchar2;
--获取当前日期和时间
Function Fn_GetCurrentDateTime Return Varchar2;
--获取数字型当前日期和时间
Function Fn_GetNumbericCurrentDateTime Return Varchar2;
--获取当年的第一天
Function Fn_GetCurrentYearStartDate Return Varchar2;
--获取当年的最后一天
Function Fn_GetCurrentYearEndDate Return Varchar2;
--获取当前时间
Function Fn_GetCurrentTime Return Varchar2;
End Pkg_Stm_Date;
CREATE OR REPLACE Package Body Pkg_Stm_Date As
--Author:chenqingchang
--Purpose:相关日期处理功能包
--获取某一天是第几周
Function Fn_GetWeekbyDate(P_Date Varchar2) Return Varchar2
Is
Begin
Return To_char(To_Date(P_Date,'yyyy-mm-dd'),'fmiw');
End Fn_GetWeekbyDate;
--获取某一天是第几周
Function Fn_GetWeekbyDate(P_Date Date) Return Varchar2
Is
Begin
Return To_Char(P_Date,'fmiw');
End Fn_GetWeekbyDate;
--获取某一天是周几
Function Fn_GetWeekDaybyDate(P_Date Varchar2) Return Varchar2
Is
Begin
Return to_char(To_Date(P_Date,'yyyy-mm-dd'),'day','nls_date_language=''simplified chinese''');
End Fn_GetWeekDaybyDate;
--获取某一天是周几
Function Fn_GetWeekDaybyDate(P_Date Date) Return Varchar2
Is
Begin
Return to_char(P_Date,'day','nls_date_language=''simplified chinese''');
End Fn_GetWeekDaybyDate;
--获取该日期所在的周是单周还是双周
Function Fn_GetWeekTypeByDate(P_Date Varchar2) Return Pls_Integer
Is
Begin
Return Mod(Pkg_Stm_Date.Fn_GetWeekbyDate(P_Date), 2);
End Fn_GetWeekTypeByDate;
--根据查询日期,获取该日期所在的星期的星期一对应的日期
Function Fn_GetMonDayByDate(P_Date Varchar2) Return Varchar2
Is
L_MonDayByDate Varchar2(19);
Begin
Select to_char(trunc(to_date(P_Date,'yyyy-mm-dd'),'iw') ,'yyyy-mm-dd')
Into
L_MonDayByDate
From dual;
Return L_MonDayByDate;
End Fn_GetMonDayByDate;
--获取日期列表,返回游标类型
Function Fn_GetDateList(P_StartDate Varchar2, P_EndDate Varchar2) Return Sys_refcursor
Is
L_CurDate Sys_refcursor;
Begin
Open L_CurDate For
Select TO_DATE(P_StartDate, 'YYYY-MM-DD') + NUMTODSINTERVAL(Level, 'day') thisDate
From dual
Connect By Level <= To_Date(P_EndDate,'yyyy-mm-dd') - To_Date(P_StartDate, 'yyyy-mm-dd');
Return L_CurDate;
End Fn_GetDateList;
--返回日期列表,以表数据方式显示可以进行表关联
Function Fn_GetDateTable(P_StartDate Varchar2, P_EndDate Varchar2) Return TblTyp_Var Pipelined
Is
L_StartDate Varchar2(20) := '';
Begin
Select to_Char(to_Date(P_StartDate,'yyyy-mm-dd') - 1,'yyyy-mm-dd')
Into L_StartDate
From dual;
For dateRow In
(
Select TO_DATE(L_StartDate, 'yyyy-mm-dd') + NUMTODSINTERVAL(Level, 'day') thisDate
From dual
Connect By Level <= To_Date(P_EndDate,'yyyy-mm-dd') - To_Date(L_StartDate, 'yyyy-mm-dd')
) loop
Pipe Row(to_char(dateRow.thisDate,'yyyy-mm-dd'));
End Loop;
Return;
End Fn_GetDateTable;
--获取当天日期
Function Fn_GetCurrentDate Return Varchar2
Is
L_CurrentDate Varchar2(19);
Begin
Select to_char(SysDate,'yyyy-MM-dd')
Into L_CurrentDate
From dual;
Return L_CurrentDate;
End Fn_GetCurrentDate;
--获取当前日期和时间
Function Fn_GetCurrentDateTime Return Varchar2
Is
Begin
Return To_Char(SysDate, 'yyyy-mm-dd hh:mm:dd');
End Fn_GetCurrentDateTime;
--生成数字型的当前日期和时间
Function Fn_GetNumbericCurrentDateTime Return Varchar2
Is
Begin
Return Replace(Replace(Pkg_Stm_Date.Fn_GetCurrentDateTime(),'-', ''),':', '');
End Fn_GetNumbericCurrentDateTime;
--获取当年的第一天
Function Fn_GetCurrentYearStartDate Return Varchar2
Is
L_CurrentYearStartDate Varchar2(19);
Begin
Select to_Char(trunc(SysDate,'yyyy'),'yyyy-mm-dd')
Into L_CurrentYearStartDate
From dual;
Return L_CurrentYearStartDate;
End Fn_GetCurrentYearStartDate;
--获取当年的最后一天
Function Fn_GetCurrentYearEndDate Return Varchar2
Is
L_CurrentYearEndDate Varchar2(19);
Begin
Select to_char(add_months(trunc(sysdate,'yyyy'),12) - 1,'yyyy-mm-dd')
Into L_CurrentYearEndDate
From dual;
Return L_CurrentYearEndDate;
End Fn_GetCurrentYearEndDate;
--获取当前时间
Function Fn_GetCurrentTime Return Varchar2
Is
Begin
Return To_Char(SysDate,'HH24:MI:SS');
End Fn_GetCurrentTime;
End Pkg_Stm_Date;
转载于:https://www.cnblogs.com/wala-wo/p/5119263.html