Oracle to_date () function usage introduction, oracleto_date

Oracle to_date () function usage introduction, oracleto_date

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 convert data using the format "yyyy-MM-dd HH: mm: ss, however, an error occurs in Oracle: "ORA 01810 format code appears twice ".

Select to_date ('1970-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 ('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 the to_date parameter in 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 ('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 day hour minute second millisecond select to_char (current_timestamp (5), 'dd-MON-YYYY HH24: MI: SSxFF ') from dual; -- returns the current time in milliseconds, 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; 

SQL function TO DATE usage

TO_DATE is to convert the data type to the Data Type of DATE, and then there is a table.
For the VARCHAR type, only strings can be read during reading. Data cannot be read by date.

The to_date function in oracle

No need to worry about the DDMONYY format.
To_date is very powerful now. Your 07JUL11 format can be recognized directly. If you write 070711, you can directly recognize it.
070711 the default value is July 7. This example is not easy to express.
For example, if you write 070611, the default value of oracle is June 7.
If you want to express the time as 070611, you need to add to_date. to_date ('123', 'mmddyy') is.
This is probably the case.