Oracle Time-Date operations ____oracle

Source: Internet
Author: User
Tags time and date
Oracle time and date operations
Sysdate+ (5/24/60/60) Delays 5 seconds based on system time
SYSDATE+5/24/60 delays 5 minutes based on system time
SYSDATE+5/24 delays 5 hours based on system time
SYSDATE+5 delays 5 days based on system time
Add_months (sysdate,-5) delayed May on a system-time basis
Add_months (SYSDATE,-5*12) Delayed 5 years on a system-time basis


Late last month: Select Last_day (Add_months (Sysdate,-1)) from dual;
Last second of the month: select Trunc (Add_months (sysdate,1), ' MM ')-1/24/60/60 from dual
Week Monday Date: Select Trunc (sysdate, ' Day ') +1 from dual


Number of days since the beginning of the year: select Ceil (Sysdate-trunc (Sysdate, "year") from dual;

Today is the first few weeks of this year: select To_char (sysdate, ' FMWW ') from dual
Today is the first few weeks of this month: SELECT to_char (sysdate, ' ww ')-To_char (TRUNC (sysdate, ' MM '), ' WW ') + 1 as "Weekofmon" from dual

Days of the Month
SELECT To_char (Last_day (sysdate), ' DD ') days from dual
Days of the Year
Select Add_months (trunc (Sysdate, ' year ')-Trunc (Sysdate, "year") from dual
The date of next Monday
SELECT next_day (sysdate, ' Monday ') from dual


============================================

--Calculating weekday methods

CREATE TABLE T (s date,e date);
Alter session Set Nls_date_format = ' Yyyy-mm-dd ';
INSERT into t values (' 2003-03-01 ', ' 2003-03-03 ');
INSERT into t values (' 2003-03-02 ', ' 2003-03-03 ');
INSERT into t values (' 2003-03-07 ', ' 2003-03-08 ');
INSERT into t values (' 2003-03-07 ', ' 2003-03-09 ');
INSERT into t values (' 2003-03-05 ', ' 2003-03-07 ');
INSERT into t values (' 2003-02-01 ', ' 2003-03-31 ');

--This assumes that the date is not with time, otherwise add trunc before all the dates.
Select S,e,e-s+1 Total_days,
Trunc ((e-s+1)/7) *5 + length (replace (substr (' 01111100111110 ', to_char (S, ' d '), mod (e-s+1,7)), ' 0 ', ') work_days
From T;

--drop table t;

Cited here: http://www.itpub.net/showthread.php?s=1635506cd5f48b1bc3adbe4cde96f227&threadid=104060&perpage=15 &pagenumber=1
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: 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.