Common Oracle date functions!

The common time format mask is as follows:
Mask element meaning
YYYY four-digit year (for example, 2005) year
YY two-digit year (for example, 05)
Q quarter (1-4)
MM month (01-12) month
The number of weeks in WW (1-53), in which the first week is the first day to the seventh day of the year.
The number of weeks in the month of W (1-5), in which the first week is the first day of the month to the seventh day
Day of DDD (1-366)
DD Day (1-31)
Day of week D (1-7), where Sunday is 1 and Saturday is 7
HH24 in 24-hour format (0-23) hour
MI minute (0-59) minute
SS seconds (0-59) second
SSSSS seconds after midnight (0-86399)


There are two very good functions that can be used to operate the date or numeric value:
Round (date, 'specifies the date netmask ') returns the rounding result of the date and time.
If the specified mask is different, the result is different.
Year by July 1
Month is the dividing line of 16
The day is divided by 12 noon.
Hh takes 30 minutes as the dividing line
Mi takes 30 seconds as the dividing line

Trunc (date, 'specified date netmask ') returns the truncation time
Year, January 1 of the current year
Month 1 of this month

Example: (Note: The difference between day d dd)
SQL> select round (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'Year') from dual;
ROUND (TO_DATE ('2014: 31: 4
------------------------------
2007-1-1
 
SQL> select round (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'month') from dual;
ROUND (TO_DATE ('2014: 31: 4
------------------------------
2007-6-1
 
SQL> select round (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'mm') from dual;
ROUND (TO_DATE ('2014: 31: 4
------------------------------
2007-6-1
 
SQL> select round (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'day') from dual;
ROUND (TO_DATE ('2014: 31: 4
------------------------------
2007-5-20
 
SQL> select round (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'D') from dual;
ROUND (TO_DATE ('2014: 31: 4
------------------------------
2007-5-20
 
SQL> select round (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'dd') from dual;
ROUND (TO_DATE ('2014: 31: 4
------------------------------
2007-5-17

SQL> select trunc (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'Year') from dual;
TRUNC (TO_DATE ('2017: 31: 4
------------------------------
2007-1-1
 
SQL> select trunc (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'month') from dual;
TRUNC (TO_DATE ('2017: 31: 4
------------------------------
2007-5-1
 
SQL> select trunc (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'mm') from dual;
TRUNC (TO_DATE ('2017: 31: 4
------------------------------
2007-5-1
 
SQL> select trunc (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'day') from dual;
TRUNC (TO_DATE ('2017: 31: 4
------------------------------
2007-5-13
 
SQL> select trunc (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'D') from dual;
TRUNC (TO_DATE ('2017: 31: 4
------------------------------
2007-5-13
 
SQL> select trunc (to_date ('2014 16:31:46 ', 'yyyymmdd hh24: mi: ss'), 'dd') from dual;
TRUNC (TO_DATE ('2017: 31: 4
------------------------------
2007-5-16

Date and time functions:
Add_months (date, number) specifies the date to be postponed by number months
Last_day (date) specifies the last day of the current month
New_time (date, abbreviated time zone) to adjust the time zone
Next_day (date, number) number indicates the number of weeks, and Sunday is 1. specify the date of the number (within a week or after a week)
Months_between (date 1, date 2) There are several months between date 1 and date 2
Date and time of the Current sysdate System
Code for practice:

Select to_number (to_char (last_day (add_months (to_date ('201312', 'yyyymmdd'),-1) + 1, 'yyyymmdd') from dual;
 
TO_NUMBER (TO_CHAR (LAST_DAY (ADD
------------------------------
20040401

Select to_number (to_char (last_day (to_date ('201312', 'yyyymmdd'), 'yyyymmdd') from dual;

TO_NUMBER (TO_CHAR (LAST_DAY (ADD
------------------------------
20040430

Create or replace procedure p_hkb_date_insert is
/*
Process Function Description: Date inserted Table
*/

V_days number (10 );
V_date date;
I number (10 );

Begin

Begin
-- Get the number of days in the current month
Select to_number (to_char (last_day (sysdate), 'dd '))
Into v_days
From dual;
-- Select sysdate from dual; current date
-- Select last_day (sysdate) from dual; End Date
-- Select last_day (add_months (sysdate,-1) from dual; last month's date
-- SELECT to_char (last_day (SYSDATE), 'dd') days FROM dual; number of days in the current month
-- Select last_day (add_months (sysdate,-1) + 1 from dual; the first day of the current month
-- Select to_number (to_char (sysdate, 'yyyymmdd') from dual; the current system date is converted to a format such as 20070910:

End;
I: = 1;
Begin
Select last_day (add_months (sysdate,-1) into v_date from dual;
While I <= v_days

Loop
Insert into hkb_date
Values
(V_date + I,
To_char (v_date + I, 'yyyymmdd '),
To_number (to_char (v_date + I, 'yyyymmdd ')));
-- Insert into hkb_date
-- (Float_date)
-- Values
-- (To_char (v_date + I, 'yyyymmdd '));
-- Insert into hkb_date
-- (Number_date)
-- Values
-- (To_number (to_char (v_date + I, 'yyyymmdd ')));
I: = I + 1;
End loop;
End;
End p_hkb_date_insert;

Create table hkb_date_construct as select * from hkb_date where 1 = 2; inherit table fields

Create table hkb_date_data as select * from hkb_date; inherit table records