Oracle Time function (sysdate) in-depth understanding

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

A brief analysis of Oracle time function (sysdate)
1: Get the current date is the week of the month
Sql> Select To_char (sysdate, ' YYYYMMDD W HH24:MI:SS ') from dual;
To_char (sysdate, ' YY
-------------------
20030327 4 18:16:09
Sql> Select To_char (sysdate, ' W ') from dual;
T
-
4
2: Get the current date is the day of the one week, note that Sunday is the first
Sql> Select Sysdate,to_char (sysdate, ' D ') from dual;
Sysdate T
--------- -
27-MAR-03 5
Similar:
Select To_char (sysdate, ' yyyy ') from dual; -year
Select To_char (sysdate, ' Q ' from dual;--season
Select To_char (sysdate, ' mm ') from dual; --month
Select To_char (sysdate, ' DD ') from dual; --Day
The day of the DDD year
The first few weeks of the WW year
W the first few weeks of the month
Day of the week in D week
HH hours (12)
Hh24 hours (24)
Mi points
SS sec
3: Take the current date is the day of the week in Chinese display:
Sql> Select To_char (sysdate, "Day") from dual;
To_char (sysdate, ' Day ')
----------------------

Thursday
4: If a table has an index on a field of date type, how to use
Alter session set nls_date_format= ' Yyyy-mm-dd HH24:MI:SS '

5: Get the current date
Select Sysdate from dual;
6: Get the date of the day 0:0 A.M. 0 seconds
Select Trunc (sysdate) from dual;
--Get the last second of the day
Select Trunc (sysdate) + 0.99999 from dual;
--Get the exact number of hours
Select Trunc (sysdate) + 1/24 from dual;
Select Trunc (sysdate) + 7/24 from dual;
7. Get the date of tomorrow 0:0 A.M. 0 seconds
Select Trunc (sysdate+1) from dual;
Select Trunc (sysdate) +1 from dual;
8: Date of the month
Select Trunc (sysdate, ' mm ') from dual;
9: Get the day of next month
Select Trunc (Add_months (sysdate,1), ' mm ') from dual;

10: Return the last day of the current month?
Select Last_day (sysdate) from dual;
Select Last_day (trunc (sysdate)) from dual;
Select Trunc (Last_day (sysdate)) from dual;
Select Trunc (Add_months (sysdate,1), ' mm ')-1 from dual;
11: Get every day of the year
Select Trunc (sysdate, ' yyyy ') + rn-1 Date0
From
(select RowNum rn from All_objects
where rownum<366);
12: Today is the nth day of the year
SELECT to_char (sysdate, ' DDD ') from DUAL;
13: How to add 2 years to an existing date
Select Add_months (sysdate,24) from dual;
14: Determine whether a day is the year of the run year
Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;
15: Judge whether the year after two years
Select Decode (To_char (Last_day (Add_months (sysdate,24), ' y '), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;
16: Get the date of the quarter
Select Ceil (To_number (To_char (sysdate, ' mm '))/3) from dual;
Select To_char (sysdate, ' Q ') from dual;

Oracle Time function (sysdate) in-depth understanding

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.