In-depth understanding of ORACLE time functions (SYSDATE)

Source: Internet
Author: User

Addition
Select sysdate, add_months (sysdate, 12) from dual; -- add 1 year
Select sysdate, add_months (sysdate, 1) from dual; -- add January
Select sysdate, to_char (sysdate + 7, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 week
Select sysdate, to_char (sysdate + 1, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 day
Select sysdate, to_char (sysdate + 1/24, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 hour
Select sysdate, to_char (sysdate + 1/24/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 minute
Select sysdate, to_char (sysdate + 1/24/60/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- add 1 second

Subtraction
Select sysdate, add_months (sysdate,-12) from dual; -- minus 1 year
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 week
Select sysdate, to_char (sysdate-1, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 day
Select sysdate, to_char (sysdate-1/24, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 hour
Select sysdate, to_char (sysdate-1/24/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 minute
Select sysdate, to_char (sysdate-1/24/60/60, 'yyyy-mm-dd HH24: MI: ss') from dual; -- minus 1 second

Analysis of ORACLE time functions (SYSDATE)
1: Obtain the week number of the current month.
SQL> select to_char (sysdate, 'yyyymmdd W HH24: MI: ss') from dual;
TO_CHAR (SYSDATE, 'yy
-------------------
4 18:16:09 20030327
SQL> select to_char (sysdate, 'w') from dual;
T
-
4
2: The current date is the day of the week. Note that Sunday is the first day of the week.
SQL> select sysdate, to_char (sysdate, 'D') from dual;
SYSDATE T
----------
27-MAR-03 5
For example:
Select to_char (sysdate, 'yyyy') from dual; -- year
Select to_char (sysdate, 'q' from dual; -- quarter
Select to_char (sysdate, 'mm') from dual; -- month
Select to_char (sysdate, 'dd') from dual; -- day
The day of the year in ddd
The week in WW
W the week of the month
D. day of the week
Hh hour (12)
Hh24 (24)
Mi score
Ss seconds
3: display the current date in Chinese on the day of the week:
SQL> select to_char (sysdate, 'day') from dual;
TO_CHAR (SYSDATE, 'day ')
----------------------

Thursday
4: If a table is indexed on a date field, 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 00:00:00 that day
Select trunc (sysdate) from dual;
-- Get the last second of the day
Select trunc (sysdate) + 0.99999 from dual;
-- Obtain the hour value.
Select trunc (sysdate) + 1/24 from dual;
Select trunc (sysdate) + 7/24 from dual;
7. Get the date of 00:00:00 tomorrow morning.
Select trunc (sysdate + 1) from dual;
Select trunc (sysdate) + 1 from dual;
8: date of January 1, 1st day of this month
Select trunc (sysdate, 'mm') from dual;
9: Get the date of January 1, 1st day of next month.
Select trunc (add_months (sysdate, 1), 'mm') from dual;

10: returns 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: determines whether the year of a certain day is a runyear.
Select decode (to_char (last_day (trunc (sysdate, 'y') + 31), 'dd'), '29', 'leap year', 'Year') from dual;
15: judge whether it is a runyear after two years
Select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y') + 31), 'dd'), '29', 'leap year ', 'Year') from dual;
16: Obtain the quarter of the date
Select ceil (to_number (to_char (sysdate, 'mm')/3) from dual;
Select to_char (sysdate, 'q') 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: 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.