Addition and subtraction of Oracle time and date

Date of last month: selectlast_day (add_months (sysdate,-1) fromdual; last second of this month: selecttrunc (add_months (sysdate, 1), mm)-12460fromdual date of this week: selecttrunc (sysdate, day) + 1fromdual days from the beginning to the present

Last month's date: select last_day (add_months (sysdate,-1) from dual;
Last second of the month: select trunc (add_months (sysdate, 1), 'mm')-1/24/60/60 from dual
Date of the week: select trunc (sysdate, 'day') + 1 from dual

Days from the beginning of the year to date: select ceil (sysdate-trunc (sysdate, 'Year') from dual;

Today is the week of this year: select to_char (sysdate, 'fmww') from dual
Today is the week of the month: select to_char (sysdate, 'ww ')-to_char (trunc (sysdate, 'mm'), 'ww ') + 1 as "weekofmon" from dual

Number of days this month
Select to_char (last_day (sysdate), 'dd') days from dual
Number of days this year
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual
The date of next Monday
Select next_day (sysdate, 'monday') from dual

========================================================== ====

-- Workday Calculation Method

Create table t (s date, e date );
Alter session set nls_date_format = 'yyyy-mm-dd ';
Insert into t values ('2017-03-01 ', '2017-03-03 ');
Insert into t values ('2017-03-02 ', '2017-03-03 ');
Insert into t values ('2017-03-07 ', '2017-03-08 ');
Insert into t values ('2017-03-07 ', '2017-03-09 ');
Insert into t values ('2017-03-05 ', '2017-03-07 ');
Insert into t values ('2017-02-01 ', '2017-03-31 ');

-- Assume that all dates do not contain time; otherwise, add trunc before all dates.
Select s, e, e-s + 1 total_days,
Trunc (e-s + 1)/7) * 5 + length (replace (substr ('000000', to_char (s, 'D '), mod (e-s + 1, 7), '0', '') work_days
From t;

-- Drop table t;

========================================================== ==========================================================

Determine whether the current time is morning afternoon or evening

Select case
When to_number (to_char (sysdate, 'hh24') between 6 and 11 then 'am'
When to_number (to_char (sysdate, 'hh24') between 11 and 17 then 'more'
When to_number (to_char (sysdate, 'hh24') between 17 and 21 then''
End
From dual;