SQL instance for date in Oracle

№ 1: The current date is the week ordinal of this 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: 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 first day of the DDD year
The first few weeks of the WW year
W the first few weeks of the month
Days of the week in D week
HH Hour (12)
Hh24 hours (24)
Mi min
SS seconds

№ 3: 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 is indexed in a date-type field, how to use the

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 0:0 A.M. 0 seconds of the day
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 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: 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 this year
SELECT to_char (sysdate, ' DDD ') from DUAL;

№13: How to add 2 years to the existing date
Select Add_months (sysdate,24) from dual;

№14: To determine whether the year of a certain date is the run year
Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' excepting ') from dual;

№15: To determine whether two years after the run year
Select Decode (To_char (Last_day (Add_months (sysdate,24), ' y ') +31, ' DD '), ' 29 ', ' Leap Year ', ' excepting ') 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;

http://tb.blog.csdn.net/TrackBack.aspxPostId=1475579