实际业务场景,已知开始日期、结束日期、年份、季度。需要判断该段时间,在该年的当前季度占有多少天数。
举例:
/*
* FUNCTION NAME:get_curq_hold_day
* param:
* p_start_date 开始日期
* p_end_date 结束日期
* p_year 年份 格式:Y2018
* p_quarter 季度 格式:1
* DESCRIPTION:
* 获取一个时间段在某年的某个季度中所占有的天数。
*
* HISTORY:
* 1.00 2018-12-29 BlackHeart Creation
*/
FUNCTION get_curq_hold_day(p_start_date DATE
,p_end_date DATE
,p_year IN VARCHAR2
,p_quarter IN VARCHAR2) RETURN NUMBER IS
l_start_q NUMBER;
l_end_q NUMBER;
l_cur_hold_days NUMBER;
BEGIN
SELECT COUNT(1)
INTO l_cur_hold_days
FROM (SELECT to_char(p_start_date + rownum - 1, 'yyyymmdd') AS daylist
FROM dual
CONNECT BY rownum <= trunc(p_end_date - p_start_date) + 1)
WHERE to_char(to_date(daylist, 'yyyymmdd'), 'YYYY') || 'Q' ||
to_char(to_date(daylist, 'yyyymmdd'), 'Q') =
REPLACE(p_year, 'Y', '') || 'Q' || p_quarter
GROUP BY to_char(to_date(daylist, 'yyyymmdd'), 'YYYY') || 'Q' ||
to_char(to_date(daylist, 'yyyymmdd'), 'Q');
RETURN l_cur_hold_days;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
Test:
参数名称 | 参数类型 | 参数值 |
---|---|---|
p_start_date | Date | 2018/3/15 |
p_end_date | Date | 2018/10/18 |
p_year | String | Y2018 |
p_quarter | String | 1 |
result | Float | 17 |