To_date () function usage

Spl>Select * from emp
Where dates
Between
To_date ('2017-06-12 10:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
And
To_date ('2017-06-12 10:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')

When the input parameters corresponding to HH, MI, and SS are omitted, Oracle uses 0 as the DEFAULT value. If the input date data ignores the time part, Oracle sets the hour, minute, and second part to 0, that is, it will take the whole day.

Similarly, if the DD parameter is ignored, Oracle uses 1 as the default value of the day, that is, it takes the entire month.

However, do not be confused by this kind of "inertia". If the MM parameter is ignored, Oracle will not take the entire year and the entire month.

Note:

1. when Oracle's to_date function is used for date conversion, the format of "yyyy-MM-dd HH: mm: ss" may be intuitively used for conversion, however, an error occurs in Oracle: "ORA 01810 format code appears twice ". For example, select to_date ('1970-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mm: ss') from dual;
The reason is that SQL statements are case-insensitive. MM and mm are considered to be the same format code, so Oracle SQL uses mi instead of minutes. Select to_date ('1970-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mi: ss') from dual;
2. It will be displayed in the form of 24 hours with HH24
Select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual; // mi is minute
Select to_char (sysdate, 'yyyy-MM-dd HH24: mm: ss') from dual; // mm displays the month

Several SQL instances on date in Oracle

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
D. day of the week
Hh hour (12)
Hh24 (24)
Mi score
Ss seconds

Week 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

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;