Oracle to_date () function usage

In Oracle databases, the Oracle to_date () function is a frequently used function. The following describes the usage of the Oracle to_date () function in detail, hoping to enlighten you.

To_date () and 24-hour notation and display of mm minutes:

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 ".

 
 

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

For example:
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('2005-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 meaning of the to_date parameter in the month oracle.

1. 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 day of 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(’2003-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. The precision after the second can be specified (max = 9)
Select to_char (current_timestamp (9), 'mi: SSxFF ') from dual;

6. computing program running time (MS)

 
 

  
  
declare   
  
  
type rc is ref cursor;   
  
  
l_rc rc;   
  
  
l_dummy all_objects.object_name%type;   
  
  
l_start number default dbms_utility.get_time;   
  
  
begin   
  
  
for I in 1 .. 1000   
  
  
loop   
  
  
open l_rc for   
  
  
’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; 
 
 

Oracle TRIM function syntax

Oracle date functions

Introduction to Oracle System Variable Functions

Provides you with an in-depth understanding of Oracle triggers

Syntax for creating an Oracle package