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

1. Date interval operation

Current Time minus 7 minutes
Select sysdate, sysdate-interval '7' MINUTE from dual
Current Time minus 7 hours
Select sysdate-interval '7' hour from dual
Current Time minus 7 days
Select sysdate-interval '7' day from dual
Current Time minus July
Select sysdate, sysdate-interval '7' month from dual
Current Time minus 7 years
Select sysdate, sysdate-interval '7' year from dual
Time Interval multiplied by a number
Select sysdate, sysdate-8 * interval '2' hour from dual

2. Date-to-character operations
Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-ddd hh: mi: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm iw-d hh: mi: ss') from dual
Refer to the oracle documentation (ORACLE901DOC/SERVER.901/A90125/SQL _ELEMENTS4.HTM #48515)

3. Character-to-date operations
Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: mi: ss') from dual
The usage is similar to that of to_char.

4. Use of the trunk/ROUND Function
Select trunc (sysdate, 'Year') from dual
Select trunc (sysdate) from dual
Select to_char (trunc (sysdate, 'yyyy'), 'yyyy') from dual

5. oracle has millisecond-level data types
-- Returns the current time year month hour minute second millisecond
Select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual;
-- Returns the second millisecond of the current time. The precision after the second can be specified (max = 9)
Select to_char (current_timestamp (9), 'mi: SSxFF ') from dual;

6. computing program running time (MS)
Declare
Type rc is ref cursor;
Rochelle rc;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Begin
For I in 1000
Loop
Open l_rc
'Select object_name from all_objects '|

'Where object_id = '| I;
Fetch l_rc into l_dummy;
Close l_rc;
End loop;
Dbms_output.put_line
(Round (dbms_utility.get_time-l_start)/100, 2) |
'Seconds ...');
End;