Oracle Time Operation __oracle

Directory
=========================================
1.Oracle Date function
2. Date plus minus
3. Month Plus and minus
4. Year Plus and minus
5. Ask for the last day of the month
6. Ask for the first day of the month
7. For the next one weeks

Introductory Knowledge:

Date-time storage in ①oracle:
Data stored in a time format in an Oracle database is stored in an Oracle-specific format, accounting for 7 bytes, regardless of the time format displayed at the query. A time unit that does not store the seconds below.
Date-time display in ②oracle:
Typically, after the client has established a connection to the database, Oracle gives the default time format data to be displayed in relation to the character set being used. Usually displays the month and the year, but does not show time and seconds.
Date Time inserts in ③oracle:
When inserting data into a table, if you do not use a transform function, the format of the time field must conform to the time format of the session environment, or it cannot be inserted.
Date-time format modification in ④oracle:
A.sql> alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';
B. Table/HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0 Add a string (8i version) to the primary key, the string name is Nls_date_format, the string value is the time format you want to define
The former is valid only for the current session, which means that once you close the Sql*plus window or reopen a sql*plus window, the date-time format still takes the date-time format of the local character set. The latter is valid for all client applications. When both are applied, the modification of ALTER session shall prevail.


Oracle's Date function:

Oracle provides a large number of date functions from 8i, including the ability to add and subtract dates, transform them, and intercept them. The following is a list of date functions provided by Oracle

Function	Use
Add_months	Adds months to a date
Last_day	Computes the last day of the month
Months_between	Determines the number of months between two dates
New_time	Translates a time to a new time zone
Next_day	Returns the date of the next specified weekday
ROUND	Rounds a Date/time value to a specified element
Sysdate	Returns the current date and time
To_char	Converts dates to strings
To_date	Converts strings and numbers to dates
TRUNC	Truncates a date/time value to a specific element

ii. date Plus and minus:

In Oralce, the default unit for adding and reducing a date is the day, which means that if we add 1 to the current date, we add a day instead of a second or an hour. So how do you want to add and subtract some time of the day? Very simple. Just convert them to days.

"1" for the current time plus 30 minutes: The month plus and minus date plus and minus compared to a lot more difficult, because the number of days per month is not fixed, may be 31,30,29,28. If you use the above method to convert the month to the actual number of days will inevitably appear multiple judgments, thanks to Oracle to provide us with a add_months function, this function will automatically determine the number of days of the month. Take a look at the following example:

"1" adds 6 months to the current time: SQL > select To_char (sysdate, ' yyyy-mm-dd hh:mi:ss ') now_date,
2 To_char (sysdate+ (30/24/60), ' Yyyy-mm-dd hh:mi:ss ') new_date
3 from dual;

Now_date new_date
-- ------------------------------------ --------------------------------------
2008-06-30 10:47:31 2008-06-30 11:17:31

SQL >

We saw the use of 30/24/60 in the green highlight to convert the minutes to day by day. Another point to note is: Sql*plus environment, the default date format: Nls_date_format is DD-MM-YYYY, is not included in time, minutes, seconds, so we have to use the To_char way to specify the date format entered.

In addition, you can modify the default date output format by executing the following statements in Sql*plus so that you do not need to convert through the To_char to directly output the line. Alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';

"2" subtracts 30 minutes from the current time: SQL > select To_char (sysdate + (-30/24/60), ' Yyyy-mm-dd hh:mi:ss ') new_date from dual;

New_date
-- ------------------------------------
2008-06-30 10:24:59

You just need to add a negative number.

Third, month plus and minus:
Sql> Select Add_months (sysdate, 6) from dual;

Add_months
----------
3 January-December-08

"2" for the current time minus 6 months: sql> Select Add_months (sysdate,-6) from dual;

Add_months
----------
3 January-December-07

"3" for two date difference between the number of months:

Typically, a two-time subtraction will result in the number of days, but sometimes we prefer to get results in months, and if manual conversions are too cumbersome, Oracle provides a function that is months_between. SQL > select Months_between (sysdate,
2 to_date (' 2008-01-01 01:00:00 ', ' yyyy-mm-dd hh:mi:ss ') result
3 from dual;

Result
-- --------
5.94928203

The Months_between function has 2 parameters, the first argument is the end date, and the second parameter is the start date, and Oracle uses the first argument to subtract the second parameter to get the number of months. So the results are likely to be negative.

Iv. year Plus and minus:

Oracle does not directly provide a function to add and subtract the year, but with the add_months and Months_between functions, we can do so.

"1" adds 2 years to the current date: SQL > select Add_months (sysdate, 2*12) two_years_later
2 from dual;

Two_years_
-- --------
30月-June-10

"2" for a few years for two dates: SQL >   Select  months_between (sysdate, 
   2    & Nbsp;     to_date (' 2006-06-30 ',  ' yyyy-mm-dd '))  / 12  years_between
   3      from  dual;