To in Oracle Database_ Usage of the date() function

Posted by $SuperString on Mon, 01 Jun 2020 17:15:37 +0200

In an Oracle database, Oracle to_ The date () function is a frequently used function. Here is a detailed introduction to Oracle to_ Usage of the date() function

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

1, Using Oracle's to_ When the date function is used for date conversion, many Java programmers may directly use the format "yyyy MM DD HH: mm: SS" as the format for conversion, but in Oracle, an error will be caused: "ORA 01810 format code appears twice".

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

Because SQL is not case sensitive, mm and mm are considered to be the same format code, so Oracle's SQL uses mi instead of minutes.

to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss')

2, Another 24-hour display is HH24

to_date(sysdate,'yyyy-MM-dd HH24:mi:ss') //mi is a minute
to_date(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm displays the month to in oracle_ Date parameter meaning

1. Date format parameter meaning description

D Day of the week  
DAY The name of the day, filled with spaces to 9 characters  
DD Day of the month  
DDD Day of the year  
DY Short name of the day  
IW ISO Standard week of the year  
IYYY ISO Standard four digit year  
YYYY Four digit year  
YYY,YY,Y Last three digits of the year, two digits, one digit  
HH Hours, 12 hours  
HH24 Hours, 24 hours  
MI branch  
SS second  
MM month  
Mon Shorthand for month  
Month Full name of the month  
W Week of the month  
WW Week 1 of the year.Date time interval operation 
//Current time minus 7 minutes 
select sysdate,sysdate - interval '7' MINUTE from dual 
//Current time minus7Hours of time 
select sysdate - interval '7' hour from dual 
//Current time minus7Days of time 
select sysdate - interval '7' day from dual 
//Current time minus7Time of month 
select sysdate,sysdate - interval '7' month from dual 
//Current time minus7Year time 
select sysdate,sysdate - interval '7' year from dual 
//Time interval times a number 
select 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 type

  • - returns the current time, month, day, hour, minute, second and millisecond
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual; 
  • - returns the seconds and milliseconds of the current time. You can specify the precision after seconds (maximum = 9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;

Topics: Oracle SQL Database Java