Oracle's to_date function detailed explanation

Oracle's to_date() function

In Oracle database, the Oracle to_date() function is a function we often use. Here is a detailed introduction to the usage of the Oracle to_date() function.

to_date() and 24-hour representation and mm minute display:

1. Use Oracle's to_date function to do date conversion

Many Java programmers may directly use the format of "yyyy-MM-dd HH:mm:ss" as the format for conversion.

But in Oracle it will cause an error:"ORA 01810 format code appears twice"。

to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') //Wrong writing

The reason is that Oracle's SQL is case-insensitive, and MM and mm are considered to be the same format code, so Oracle's SQL adoptsmiReplace minutes.

to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss')//Correct writing//Because Oracle's SQL is not case sensitive, the following writing method is the same as the above and is also correct.to_date('2005-01-01 13:14:20','yyyy-mm-dd HH24:mi:ss')//Correct writingto_date('2005-01-01 13:14:20','YYYY-MM-dd HH24:mi:ss')//Correct writing

2. Date format parameters Meaning description

D What day of the week  
DAY The name of the sky，Use spaces to fill to9Characters  
DD What day of the month  
DDD What day of the year  
DY The abbreviation of heaven  
IW ISOWhat week of the standard year  
IYYY ISOStandard four-digit year  
YYYY Four years  
YYY,YY,Y The last three of the year，Two，One  
HH Hour，according to12Hour计  
HH24 Hour，according to24Hour计  
MI point  
SS Second  
MM moon  
Mon moon份的简写  
Month moon份的全名  
W 该moon的第几个星期  
WW What week of the year  1.Date and time interval operation 
//The current time is minus 7 minutes of timeselect sysdate,sysdate - interval '7' MINUTE from dual 
//The current time is minus 7 hours of timeselect sysdate - interval '7' hour from dual 
//The current time minus 7 daysselect sysdate - interval '7' day from dual 
//The current time minus the time of Julyselect sysdate,sysdate - interval '7' month from dual 
//The current time minus 7 years timeselect sysdate,sysdate - interval '7' year from dual 
//Multiply the time interval by a numberselect sysdate,sysdate - 8 *interval '2' hour from dual

Date to character operation

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

Character to date operation

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

Oracle has millisecond data types

– Return to the current time, year, month, day, hour, minute, second, millisecond

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

– Returns the milliseconds of the current time, and the accuracy after the second can be specified (maximum = 9)

select to_char(current_timestamp(9),'MI:SSxFF') from dual;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.