Tips for Oracle Time Application

1. Get the current date
Select sysdate from dual;
2. Get the date of 00:00:00 on the current day.
Select trunc (sysdate) from dual;
3. display the current date in Chinese on the day of the week:
Select to_char (sysdate, 'day') from dual;
4. The current Day is the day of the week.
Select sysdate, to_char (sysdate, 'D') from dual;
Note: Sunday is the first day.
Shape:
'Yyyy'
'Q' quarter
'Mm' month
'Dd' day
Day of 'ddd'
The week in 'ww'
'W' the week of the month
Day of the week
'D 'day of the week
5. The day of the next week
Next Friday: select next_day (sysdate, 'Friday') "next Friday" from dual;
Select next_day (sysdate, 4) from dual;
6. Obtain the week of the current month.
Select to_char (sysdate, 'w') from dual;
Select to_char (sysdate, 'yyyymmdd W HH24: MI: ss') from dual;
7. Get the last second of every day
Select trunc (sysdate) + 0.99999 from dual;
8. Get the date of 00:00:00 tomorrow morning.
Select trunc (sysdate + 1) from dual;
Or select trunc (sysdate) + 1 from dual;
9. date of January 1, 1st day of this month
Select trunc (sysdate, 'mm') from dual;
1st day of next month
Select trunc (add_months (sysdate, 1), 'mm') from dual;
11. 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;
12. The week of the year
Select to_char (sysdate, 'iw') from dual;
13. Today is the day of the year
Select to_char (sysdate, 'ddd ') from dual;
14. How can I add 2 years to an existing date?
Select add_months (sysdate, 24) from dual;
15. Determine if 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;
16. determine 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;
17. Obtain the quarter of the date
Select ceil (to_number (to_char (sysdate, 'mm')/3) from dual;
Or select to_char (sysdate, 'q') from dual;
18. query the days of the two-day period:
FLOOR: returns the largest integer equal to or less than x:
Select floor (to_date ('20170101', 'yyyymmdd')-to_date ('20170101', 'yyyymmdd') from dual;
-- Get hour
Select extract (hour from timestamp '2017-06-06 06:06:06 ') from dual;
-- Get month
Select extract (month from sysdate) from dual;
-- Calculate how many days a year
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual;
-- Note: trunc (sysdate, 'Year') truncation generation => '2017-06-06 ';
Select trunc (sysdate, 'Year') from dual;
-- Query the day of the year
Select TO_CHAR (SYSDATE, 'ddd '), sysdate from dual;
Next_day (sysdate, 6) is the next Friday from the current start. The following number is counted from Sunday.
1 2 3 4 5 6 7
October 6, 1234
Select next_day (to_date ('2017-06-06 ', 'yyyy-MM-DD'), 6 );
-- Returns the last day of a month.
Select last_day (date, '2017-06-06 ') D from dual;
Select last_day (to_date ('1970-06-06 ', 'yyyy-MM-DD') from dual;
-- Returns the day of the week on the first day of a month.
Select to_char (sysdate-(to_char (sysdate, 'dd')-1), 'day') from dual
-- Returns the last day of a month as the day of the week.
Select to_char (last_day (to_date ('1970-06-06 ', 'yyyy-MM-DD'), 'day') from dual;
-- Get last day of last month:
Select to_char (add_months (last_day (sysdate),-1), 'yyyy-MM-dd') LastDay from dual;
==> Note: last_day (sysdate) indicates the last day of the month of sysdate.
Add_months (last_day (sysdate),-1) indicates the last day of the last month
-- Last month and today
Select to_char (add_months (sysdate,-1), 'yyyy-MM-dd') PreToday from dual;
-- First day of last month
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyy-MM-dd') firstDay from dual
Select * from aaa where rq>; = '2017-06-06 'and rq <= '2017-12-06'