16 Oracle query date statements

The query date is an operation that you often encounter when using the Oracle database. The following lists some Oracle query date statements. If you are interested, take a look.

Oracle query date statements are frequently used when we use databases. The following describes 16 Oracle query date statements. Each Oracle query date statement provides a function, hope to help you.

WEEK 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

Week 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

Day of the week
D The number mapped today

'1', 'sunday', '2', 'monday', '3', 'tuesday', '4', 'wedday', '5', 'thurs ', '6', 'Friday', '7', 'satur'
HH hour (12)
Hh24 (24)
Mi score
SS seconds

Week 3: the current date is the day of the week:

SQL> select to_char (sysdate, 'day') from dual;

To_char (sysdate, 'day ')
----------------------
Thursday

Partition 4: If a table is indexed on a date field, how can we use

Alter session set nls_date_format = 'yyyy-MM-DD hh24: MI: ss'

Expected 5: Get the current date
Select sysdate from dual;

Listen 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;

April 8: The date of April 1st day of this month
Select trunc (sysdate, 'mm') from dual;

April 9: Get the date of April 1st day of next month.
Select trunc (add_months (sysdate, 1), 'mm') from dual;

Limit 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;

11th: Get every day of the year
Select trunc (sysdate, 'yyyy') + rn-1 date0
From
(Select rownum rn from all_objects
Where rownum <366 );

Listen 12: Today is the nth day of this year
Select to_char (sysdate, 'ddd ') from dual;

Lifecycle 13: how to add 2 years to an existing date
Select add_months (sysdate, 24) from dual;

April 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;

Limit 15: determines whether the year is a year of profit after two years.
Select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y') + 31), 'dd'), '29', 'leap year ', 'Year') from dual;

Quarter 16: Get the quarter of the date

Select Ceil (to_number (to_char (sysdate, 'mm')/3) from dual;

Select to_char (sysdate, 'q') from dual;

The preceding section describes the Oracle query date statement.