Oracle Time Function to_date () __ static function

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 ')

When omitting the input parameters for HH, MI, and SS, Oracle uses 0 as the default value. If the date data that you enter ignores the time part, Oracle will set the time, minute, and second portions to 0, which means that it will take the whole day.

Similarly, by ignoring the DD parameter, Oracle takes 1 as the default value for the day, which means the whole month is taken.

However, do not be fooled by this "inertia", if the mm parameter is ignored, Oracle will 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 convert the format of "Yyyy-mm-dd HH:mm:ss" into a format, but it can cause errors 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 SQL uses MI instead of minutes. Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;
2. Another 24 hours to display the form to use 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 of date in Oracle

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: The date of the day 0:0 A.M. 0 seconds
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: 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 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: Determine whether the year is a run year
Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' excepting ') from dual;

№ 15: 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: Quarterly of the date

Select Ceil (To_number (To_char (sysdate, ' mm '))/3) from dual;

Select To_char (sysdate, ' Q ') from dual;