Oracle Time Operation __oracle

Source: Internet
Author: User
Tags time and seconds

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




Adds months to a date


Computes the last day of the month


Determines the number of months between two dates


Translates a time to a new time zone


Returns the date of the next specified weekday


Rounds a Date/time value to a specified element


Returns the current date and time


Converts dates to strings


Converts strings and numbers to dates


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


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;

-- ------------------------------------
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;

3 January-December-08

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

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;

-- --------

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;

-- --------

"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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.