What do you know about Oracle date operations?

The daily work does not process much date. Recently, a time dimension table needs to be designed. This opportunity is used to sort out common date processing methods.

Oracle provides several basic date processing functions

Function	Description
Add_months (date, count)	Add count months to the specified date
Last_day (date)	Returns the last day of the month of date.
Months_between (date1, date2)	Returns the number of different months.
New_time (date, 'this', 'other ')	Convert time from this time zone to other Time Zone
Next_day (date, 'day ')	Returns the date of the first day of the week after the specified date. 'day' is the day of the week, or a number ranging from 1 to 7. 1 indicates Sunday, and so on.
Sysdate	Obtain the current system date
Current_timestamp ()	Current time and date value

Sample Code:

Select add_months (sysdate, 2) as "date two months later ",
Last_day (sysdate) as "last day of the month ",
Months_between (sysdate, to_date ('1970-12-20 ', 'yyyy-mm-dd') as "month difference ",
New_time (sysdate, 'cst ', 'est') as "Beijing time to eastern time ",
Next_day (sysdate, 1) as "Date of next Sunday ",
Sysdateas "Current System Time"
From dual

Returned results:

Date Two Months Later
Last day of this month	6/30/2012 :56
Month difference	-5.752772177
Beijing time to eastern time	6/27/2012 16: 56
Next Sunday date
Current System Time	6/27/2012 :56

 

In fact, it is often a headache for Computing Based on Time units, such as year, quarter, month, week, day ), hour, minute, second, and millisecond ). (Not discussed in milliseconds)

To obtain the time of each unit, we usually have two methods:

Example:

Trunc (sysdate, 'mi ') returns the date and time precise to 'part'

To_char (sysdate, 'mi') obtains the 'quantity' value of the time.

 

Year-Based Computation:

-- Get the first day of the year Select trunc (sysdate, 'y') from dual; -- Get the last day of the year Select add_months (trunc (sysdate, 'yyyy'), 12)-1 from dual; -- Get every day of the year Select trunc (sysdate, 'yyyy') + rn-1 date0 From (select rownum rn from all_objects where rownum <366) T; -- Today is the nth day of this year Select to_char (sysdate, 'ddd ') from dual; -- Add two years to the existing date Select add_months (sysdate, 24) from dual; -- Determines whether the year or minute of a day is a runyear. Select decode (to_char (last_day (trunc (sysdate, 'y') + 31), 'dd '), '29 ', 'Leap year ', 'Year ') From dual; -- Renewal 15: judge whether the year is a runner-up after two years Select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y') + 31 ), 'Dd '), '29 ', 'Leap year ', 'Year ') From dual;

Quarterly calculation:

-- The first day of this quarter Select to_char (trunc (sysdate, 'q'), 'yyyy-MM-DD ') from dual; -- Last day of the quarter Select add_months (trunc (sysdate, 'q'), 3)-1 from dual; -- Last day of the last quarter (you can use the first day of the current quarter minus 1) Select to_char (trunc (sysdate, 'q')-1, 'yyyy-MM-DD ') from dual; -- The first day of the previous quarter (the first day of the current quarter minus three months) Select to_char (add_months (trunc (sysdate, 'q'),-3), 'yyyy-MM-DD ') From dual; -- First day of the previous quarter (the last day of the month after one month in this quarter) Select to_char (last_day (add_months (trunc (sysdate, 'q'),-1), 'yyyy-mm-dd ') From dual;

Monthly calculation:

--Get the month Select to_char (sysdate, 'month') from dual; -- Obtain the nth month Select to_char (sysdate, 'mm') from dual; -- The first day of the month Select trunc (sysdate, 'mm') from dual; -- Last day of the month Select last_day (trunc (sysdate) from dual; -- The day of the current month Select to_char (sysdate, 'dd') from dual;

Weekly calculation:

-- The first day of a week Select to_char (sysdate, 'D') from dual; -- Get the day of the week Select initcap (to_char (sysdate, 'day') from dual; -- Obtain the week number of the year from the current time (based on the actual calendar) Select to_char (sysdate, 'iw') from dual; --- Calculate the week of the year from 1.1) Select to_char (sysdate, 'ww ') from dual; -- Calculate the week of the month from the first day) Select to_char (sysdate, 'w') from dual; -- The first day of the week (counted from Sunday) Selecttrunc (sysdate, 'D') from dual; -- Last day of the week (counted from Sunday) Select trunc (sysdate, 'D') + 6 from dual;

Daily calculation:

-- Obtain the start time and end time of the previous day Select to_char (to_date (to_char (sysdate-1, 'yyyy-mm-dd'), 'yyyy-mm-dd '), 'Yyyy-mm-dd hh24: MI: ss ') From dual;   Select to_char (to_date (to_char (sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd '), 'Yyyy-mm-dd hh24: MI: ss ') From dual;

Summary:

For example, most of the operations have been listed. If you have other requirements, try the above methods.

Everyone is welcome to learn and exchange!