How to Use the TO_DATE function in Oracle

The TO_DATE function is a date function in oracle and is also a very common function. Next I will introduce you to my understanding and learning notes on oracle TO_DATE.

1. When Oracle's to_date function is used for date conversion, many Java programmers may directly use the format "yyyy-MM-dd HH: mm: ss" 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. Another 24-hour format is used to display 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

Description of date format parameters

D. day of the week

The name of DAY, which is filled with spaces to 9 characters.

Day of DD month

The day of the year in DDD

Short Name of DY day

Week of the Year of the iw iso Standard

Four-digit year of the iyyy iso Standard

YYYY four-digit year

Last three digits of YYY, YY, and Y years, two digits, one digit

HH hours, at 12 hours

HH24 hours, in 24 hours

MI score

SS seconds

MM Month

Abbreviated month of Mon

Full name of Month

W the week of the month

The week in WW

1. Date interval operation

Current Time minus 7 minutes

Select sysdate, sysdate-interval '7' MINUTE from dual

Current Time minus 7 hours

Select sysdate-interval '7' hour from dual

Current Time minus 7 days

Select sysdate-interval '7' day from dual

Current Time minus July

Select sysdate, sysdate-interval '7' month from dual

Current Time minus 7 years

Select sysdate, sysdate-interval '7' year from dual

Time Interval multiplied by a number

Select sysdate, sysdate-8 * interval '2' hour from dual

2. Date-to-character operations

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-ddd hh: mi: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-mm iw-d hh: mi: ss') from dual

Refer to the oracle documentation (ORACLE901DOC/SERVER.901/A90125/SQL _ELEMENTS4.HTM #48515)

3. Character-to-date operations

Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: mi: ss') from dual

The usage is similar to that of to_char.

4. Use of the trunk/ROUND Function

Select trunc (sysdate, 'Year') from dual

Select trunc (sysdate) from dual

Select to_char (trunc (sysdate, 'yyyy'), 'yyyy') from dual

5. oracle has millisecond-level data types

Returns the current time, year, month, hour, minute, second, millisecond.

Select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual;

Returns the second millisecond of the current time. You can specify the precision after the second (max = 9)

Select to_char (current_timestamp (9), 'mi: SSxFF ') from dual;

6. computing program running time (MS)

Declare

Type rc is ref cursor;

Rochelle rc;

Rochelle dummy all_objects.object_name % type;

Rochelle start number default dbms_utility.get_time;

Begin

For I in 1000

Loop

Open l_rc

'Select object_name from all_objects'

'Where object_id = 'I;

Fetch l_rc into l_dummy;

Close l_rc;

End loop;

Dbms_output.put_line

(Round (dbms_utility.get_time-l_start)/100, 2)

'Seconds ...');

End;

1. Date and character conversion function usage (to_date, to_char)

Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') as nowTime from dual; // convert the date to a string
Select to_char (sysdate, 'yyyy') as nowYear from dual; // obtain the year of the time
Select to_char (sysdate, 'mm') as nowMonth from dual; // obtain the month of the time
Select to_char (sysdate, 'dd') as nowDay from dual; // obtain the date of the time
Select to_char (sysdate, 'hh24') as nowHour from dual; // when obtaining the time
Select to_char (sysdate, 'mi') as nowMinute from dual; // obtain the time score
Select to_char (sysdate, 'ss') as nowSecond from dual; // obtain the second of the time

Select to_date ('2017-05-07 13:23:44 ', 'yyyy-mm-dd hh24: mi: ss') from dual //

2.
Select to_char (to_date (222, 'J'), 'jsp ') from dual

Display Two Hundred Twenty-Two

3. Check the day of the week.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day') from dual;
Monday
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUAGE = American') from dual;
Monday
Set the date language
Alter session set NLS_DATE_LANGUAGE = 'American ';
You can also
TO_DATE ('1970-08-26 ', 'yyyy-mm-dd', 'nls _ DATE_LANGUAGE = American ')

4. Days of the two-day period
Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;

5. Use a time of null
Select id, active_date from table1
UNION
Select 1, TO_DATE (null) from dual;

Note that TO_DATE (null) is used)

6. month difference
A_date between to_date ('20140901', 'yyyymmdd') and to_date ('20140901', 'yyyymmdd ')
Therefore, it is not included in this range after on January 1, December 31 and before on January 1, December 1.
Therefore, when the time needs to be accurate, to_char is still necessary.

7. Date Format conflict
The input format depends on the type of the ORACLE character set you installed, for example, US7ASCII. The date format is '01-Jan-01'
Alter system set NLS_DATE_LANGUAGE = American
Alter session set NLS_DATE_LANGUAGE = American
Or write it in to_date.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUAGE = American') from dual;
Note that I just mentioned NLS_DATE_LANGUAGE. Of course there are many more,
Available
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS

8.
Select count (*)
From (select rownum-1 (rnum)
From all_objects
Where rownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd')-to_date ('2017-
02-01 ', 'yyyy-mm-dd') + 1
)
Where to_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + rnum-1, 'd ')
Not in ('1', '7 ')

Find the number of days between and except Monday and seven
Call DBMS_UTILITY.GET_TIME to subtract the result (1/100 seconds instead of milliseconds ).

9. Find the month
Select months_between (to_date ('01-31-1999 ', 'Mm-DD-YYYY'), to_date ('12-31-1998 ', 'Mm-DD-YYYY') "MONTHS" from dual;
1
Select months_between (to_date ('02-01-1999 ', 'Mm-DD-YYYY'), to_date ('12-31-1998 ', 'Mm-DD-YYYY') "MONTHS" from dual;
1.03225806451613

10. Next_day usage
Next_day (date, day)

Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D

11
Select to_char (sysdate, 'hh: mi: ss') TIME from all_objects
Note: the TIME of the first record is the same as that of the last record.
You can create a function to solve this problem.
Create or replace function sys_date return date is
Begin
Return sysdate;
End;

Select to_char (sys_date, 'hh: mi: ss') from all_objects;

12. Hours
Extract () identifies the field value of the date or interval value
Select extract (hour from timestamp '2017-02-16 2:38:40 ') from offer
SQL> select sysdate, to_char (sysdate, 'hh') from dual;

SYSDATE TO_CHAR (SYSDATE, 'hh ')
-----------------------------------------
2003-10-13 19:35:21 07

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

SYSDATE TO_CHAR (SYSDATE, 'hh24 ')
-------------------------------------------
19:35:21 19

13. Processing of the year, month, and day
Select older_date,
Newer_date,
Years,
Months,
Abs (
Trunc (
Newer_date-
Add_months (older_date, years * 12 + months)
)
) Days

From (select
Trunc (months_between (newer_date, older_date)/12) YEARS,
Mod (trunc (months_between (newer_date, older_date), 12) MONTHS,
Newer_date,
Older_date
From (
Select hiredate older_date, add_months (hiredate, rownum) + rownum newer_date
From emp
)
)

14. How to handle the uncertainty of the number of days in a month
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

16. Find the number of days this year
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual

How to deal with a leap year
To_char (last_day (to_date ('02 '|: year, 'mmyyyy'), 'dd ')
If it is 28, it is not a leap year.