oracle-date plus minus

Source: Internet
Author: User

Addition

Select Sysdate,add_months (sysdate,12) from dual; --plus 1 years

Select Sysdate,add_months (sysdate,1) from dual; --Add January

Select Sysdate,to_char (sysdate+7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 weeks

Select Sysdate,to_char (sysdate+1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 days

Select Sysdate,to_char (sysdate+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 hours

Select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 minutes

Select Sysdate,to_char (sysdate+1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 seconds

Subtraction

Select Sysdate,add_months (sysdate,-12) from dual; -Minus 1 years

Select Sysdate,add_months (sysdate,-1) from dual; --Minus January

Select Sysdate,to_char (sysdate-7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 weeks

Select Sysdate,to_char (sysdate-1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 days

Select Sysdate,to_char (sysdate-1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 hours

Select Sysdate,to_char (sysdate-1/24/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 minutes

Select Sysdate,to_char (sysdate-1/24/60/60, ' Yyyy-mm-dd HH24:MI:SS ') from dual; -Minus 1 seconds

Oracle About time/date operations

1. Date-time interval operation

Time minus 7 minutes for the current time

Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual

The current time minus 7 hours of time

Select Sysdate-interval ' 7 ' hour from dual

The current time minus 7 days

Select Sysdate-interval ' 7 ' Day from dual

Time minus July for current time

Select Sysdate,sysdate-interval ' 7 ' month from dual

Time minus 7 years in the current time

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 manipulation

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 related documentation for Oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4. htm#48515)

3. Character to date operation

Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual

The concrete usage is similar to the to_char above.

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 data types with millisecond levels

--Returns the current time, month, day, hour, minute, millisecond

Select To_char (Current_timestamp (5), ' dd-mon-yyyy HH24:MI:SSxFF ') from dual;

--Returns the second millisecond of the current time, specifying the precision after seconds (Max =9)

Select To_char (Current_timestamp (9), ' Mi:ssxff ') from dual;

6. Calculate when the program is running (MS)

Declare

Type RC is REF CURSOR;

L_RC RC;

L_dummy All_objects.object_name%type;

L_start number default dbms_utility.get_time;

Begin

For I in 1.. 1000

Loop

Open L_RC for

' Select object_name from all_objects ' | |

' WHERE object_id = ' | | I

Fetch L_RC into l_dummy;

oracle-date plus minus

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: info-contact@alibabacloud.com 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.