Oracle Common date Functions

The usual time format masks are as follows:
Mask element meaning
YYYY four-digit years (e.g. 2005) year
YY Two-digit year (e.g. 05)
Q Quarter (1-4)
MM month (01-12) month
Number of weeks of WW years (1-53), of which the first week is the first day of the year to the seventh day
W months of the week (1-5), where the first week is the first day of the month to the seventh day
The day of the DDD year (1-366)
DD Month's Day (1-31)
D Week of the Day (1-7), of which Sunday is 1, Saturday is 7 days
HH24 24-hour system (0-23) hour
MI min (0-59) minute
SS sec (0-59) second
Sssss seconds from After midnight (0-86399)


two very good functions, you can manipulate the date, you can also manipulate the value:
Round (date, ' specified date mask ') returns the rounding result of the DateTime
The result is different if you specify a different mask
Year to July 1 dividing line
Month number 16th is the dividing line.
Day takes 12 o'clock noon as the dividing line
HH takes 30 minutes as the dividing line
Mi with 30 seconds as the dividing line

Trunc (date, ' specified date mask ') returns truncation time
Year of January 1
Month number 1th.

Example: (Note: The difference between day D DD three)
Sql> Select round (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' year ') from dual;
ROUND (To_date (' 2007051616:31:4
------------------------------
2007-1-1

Sql> Select round (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' month ') from dual;
ROUND (To_date (' 2007051616:31:4
------------------------------
2007-6-1

Sql> Select round (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' mm ') from dual;
ROUND (To_date (' 2007051616:31:4
------------------------------
2007-6-1

Sql> Select round (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' Day ') from dual;
ROUND (To_date (' 2007051616:31:4
------------------------------
2007-5-20

Sql> Select round (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' d ') from dual;
ROUND (To_date (' 2007051616:31:4
------------------------------
2007-5-20

Sql> Select round (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' DD ') from dual;
ROUND (To_date (' 2007051616:31:4
------------------------------
2007-5-17

Sql> Select Trunc (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' year ') from dual;
TRUNC (To_date (' 2007051616:31:4
------------------------------
2007-1-1

Sql> Select Trunc (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' month ') from dual;
TRUNC (To_date (' 2007051616:31:4
------------------------------
2007-5-1

Sql> Select Trunc (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' mm ') from dual;
TRUNC (To_date (' 2007051616:31:4
------------------------------
2007-5-1

Sql> Select Trunc (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' Day ') from dual;
TRUNC (To_date (' 2007051616:31:4
------------------------------
2007-5-13

Sql> Select Trunc (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' d ') from dual;
TRUNC (To_date (' 2007051616:31:4
------------------------------
2007-5-13

Sql> Select Trunc (to_date (' 20070516 16:31:46 ', ' yyyymmdd hh24:mi:ss '), ' DD ') from dual;
TRUNC (To_date (' 2007051616:31:4
------------------------------
2007-5-16


Date-time Functions:
add_months(date, number) The specified date is postponed by number of months
Last_day(date) Specified date the last day of the month
New_time(Date, time zone shorthand) adjust time zone
Next_day(date, number) number indicates the number of weeks, Sunday is 1, the date of the specified number (within one week or after a week)
Months_between(date 1, date 2) a few months between date 1 and date 2
sysdateSystem of the current period that date and time
Code for Practice:

Select To_number (To_char (Last_day (to_date (' 20040406 ', ' YYYYMMDD '), -1)) +1, ' YYYYMMDD ')) from dual;

To_number (To_char (Last_day (ADD
------------------------------
20040401

Select To_number (To_char (Last_day (' 20040406 ', ' yyyymmdd '), ' YYYYMMDD ')) from dual;

To_number (To_char (Last_day (ADD
------------------------------
20040430

Create or replace procedure P_hkb_date_insert is
/*
Procedure Function Description: Date inserted in table
*/

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

Begin

Begin
--Get days of the 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; End of Month date
--SELECT To_char (Last_day (sysdate), ' DD ') days from dual; Days of 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 system current date is converted to the format 20070910:

End
I: = 1;
Begin
Select Last_day (Add_months (Sysdate,-1)) into the 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 field

CREATE TABLE Hkb_date_data as SELECT * from Hkb_date; Inheritance table records

Oracle Common date Functions