to_date function Usage

spl> SELECT * from emp
where dates
Between
To_date (' 2007-06-12 10:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
and
To_date (' 2007-06-12 10:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

Oracle uses 0 as the default value when omitting input parameters corresponding to HH, MI, and SS. If the date data entered ignores the time portion, Oracle will set the time, minute, and second portions to 0, which means rounding to day.

Similarly, when the DD parameter is omitted, Oracle takes 1 as the default value for the day, which means that it is rounded to the month.

However, do not be confused by this "inertia", if the mm parameter is ignored, Oracle does not take the whole year, rounding to the current month.

Note:

       1. When using Oracle's To_date function for date conversion, it is possible to intuitively use the format "Yyyy-mm-dd HH:mm:ss" as the format for the conversion. However, an error is raised in Oracle: "ORA 01810 format code appears two times". such as: Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mm:ss ') from dual;
The reason is that SQL is case-insensitive, mm and mm are considered the same format code, so Oracle's SQL uses MI instead of minutes. Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;
       2. To be displayed in 24-hour format with HH24
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mi:ss ' The from Dual;//mi is the minute
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mm:ss ') from DUAL;//MM displays the month

Several SQL instances in Oracle for date aspects

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: Get 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 day of the DDD year
The first few weeks of the WW year
W the first few weeks of the month
Day of the week in D week
HH hours (12)
Hh24 hours (24)
Mi points
SS sec

№ 3: Take the current date is the day of the week Chinese display:

Sql> Select To_char (sysdate, "Day") from dual;

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

№ 4: If a table has an index on a field of date type, 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 0:0 A.M. 0 seconds on 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 the date 0:0 A.M. 0 seconds tomorrow

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 date of next month
Select Trunc (Add_months (sysdate,1), ' mm ') from dual;

№10: Return to 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: Determine whether the year of the day is a year to run
Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;

№15: Judging whether after two years is the year of the run
Select Decode (To_char (Last_day (Add_months (sysdate,24), ' y '), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') 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;

to_date function Usage