Используйте следующую функцию PLSQL, чтобы получить DayDate недели.
Использование функции для получения дня месяца.
SELECT
DayofMonth(to_date('2020-03-29T01:00Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'FRI', 'LAST') LASTFRI -- 27-MAR-2020 01:00:00
,DayofMonth(to_date('2020-03-29T01:00Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'FIRST') FIRSTSUN -- 01-MAR-2020 01:00:00
,DayofMonth(to_date('2020-03-29T01:00Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'THIRD') THIRDSUN -- 15-MAR-2020 01:00:00
,DayofMonth(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'THIRD') THIRDSUN_FEB29 -- 16-FEB-2020 01:01:00
,DayofMonth(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'FOURTH') FouthSUN_FEB28 -- 28-FEB-2021 01:01:00
from dual;
PLSQL-функция: приведенная ниже функция, определяющая последний день месяца, преобразуется в число, используя TO_NUMBER()
для передачи в качестве параметра LAST_DAY()
.
create or replace FUNCTION DayofMonth
(dateOfMonth in date, dayOfWeek in varchar2 default 'SUN', weekOfMonth in varchar2 default 'LAST')
return date AS date_out date;
firstDayOfMonth number(5); lastDayOfMonth number(5);
monthFirstDayDate date; monthSecondDayDate date;
monthThirdDayDate date; monthFourthDayDate date;
monthLastDayDate date;
BEGIN
SELECT TO_NUMBER(to_char( trunc(dateOfMonth) - (to_number(to_char(dateOfMonth,'DD')) - 1), 'DD'), '9G999D99') into firstDayOfMonth FROM dual;
SELECT TO_NUMBER(to_char(trunc( add_months(trunc(dateOfMonth) - (to_number(to_char(dateOfMonth,'DD')) - 1), 1) -1 ), 'DD'), '9G999D99') into lastDayOfMonth FROM dual;
DBMS_OUTPUT.put_line('firstDayOfMonth:' || firstDayOfMonth || ', lastDayOfMonth:' || lastDayOfMonth);
select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 0)), dayOfWeek) into monthFirstDayDate FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 1)), dayOfWeek) into monthSecondDayDate FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 2)), dayOfWeek) into monthThirdDayDate FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 3)), dayOfWeek) into monthFourthDayDate FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (7), dayOfWeek) into monthLastDayDate FROM DUAL;
DBMS_OUTPUT.put_line('monthFirstDayDate:' || monthFirstDayDate || ', monthSecondDayDate:' || monthSecondDayDate);
DBMS_OUTPUT.put_line('monthThirdDayDate:' || monthThirdDayDate || ', monthFourthDayDate:' || monthFourthDayDate);
DBMS_OUTPUT.put_line('monthLastDayDate:' || monthLastDayDate);
IF (weekOfMonth = 'LAST') then
date_out := monthLastDayDate;
ELSIF (weekOfMonth = 'FIRST') then
date_out := monthFirstDayDate;
ELSIF (weekOfMonth = 'SECOND') then
date_out := monthSecondDayDate;
ELSIF (weekOfMonth = 'THIRD') then
date_out := monthThirdDayDate;
ELSIF (weekOfMonth = 'FOURTH') then
date_out := monthFourthDayDate;
ELSE
date_out := null;
END IF;
return date_out;
EXCEPTION
when others then
DBMS_OUTPUT.put_line('Date:' || dateOfMonth || ', Day:' || dayOfWeek || ', Week:' || weekOfMonth || ' / ' || sqlcode || ' / ' || SQLERRM(sqlcode));
date_out := null;
return date_out;
END;
/
Первый и последний день месяца — Oracle SQL
SELECT
to_char( trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 'DD') as firstDay
,to_char(trunc( add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 ), 'DD') as lastDay
FROM dual;
Тесты для проверки дня месяца без какой-либо функции:
select
next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'sun') as JAN_Month_Last_SUNDAY31 -- 31-JAN-2021 01:01:00
,next_day(LAST_DAY(to_date('2021-02-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'sun') as FEB_Month_Last_SUNDAY28 -- 28-FEB-2021 01:01:00
,next_day(LAST_DAY(to_date('2021-04-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'sun') as APRL_Month_Last_SUNDAY30 -- 25-APR-2021 01:01:00
,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 31, 'sun') as JAN_Month_First_SUNDAY31 -- 31-JAN-2021 01:01:00
,next_day(LAST_DAY(to_date('2021-02-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 28, 'sun') as FEB_Month_First_SUNDAY28 -- 28-FEB-2021 01:01:00
,next_day(LAST_DAY(to_date('2021-04-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 30, 'sun') as APRL_Month_First_SUNDAY30 -- 25-APR-2021 01:01:00
FROM DUAL;
select
next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'mon') as JAN_Month_Last_Mon31
,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'fri') as JAN_Month_Last_Fri31
,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 0)), 'fri') as JAN_Month_First_Fri31
,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 1)), 'fri') as JAN_Month_Second_Fri31
,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 2)), 'fri') as JAN_Month_Third_Fri31
,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 3)), 'fri') as JAN_Month_Fourth_Fri31
,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31), 'fri') as JAN_Month_Last_Fri31
FROM DUAL;
select
next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 0)), 'fri') as FEB_Month_First_Fri28
,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 1)), 'fri') as FEB_Month_Second_Fri28
,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 2)), 'fri') as FEB_Month_Third_Fri28
,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 3)), 'SUN') as FEB_Month_Fourth_Fri28
,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (7), 'fri') as FEB_Month_Last_Fri28
FROM DUAL;
select
next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 0)), 'fri') as FEB_Month_First_Fri29
,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 1)), 'fri') as FEB_Month_Second_Fri29
,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 2)), 'fri') as FEB_Month_Third_Fri29
,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 3)), 'fri') as FEB_Month_Fourth_Fri29
,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (7), 'fri') as FEB_Month_Last_Fri29
FROM DUAL;
person
Yash
schedule
22.12.2020