Description of the to_date parameter in Oracle

Date Format Parameters	Description
D	Day of the week
DAY	The name of the day, with a space of 9 Characters
DD	The day of the month
DDD	Day of the year
DY	Abbreviated name of day
IW	Week of the Year of the ISO standard
IYYY	ISO standard four-digit year
YYYY	Four-digit year
YYY, YY, Y	The last three digits of the year, two digits, one digit
HH	Hours, in 12 hours
HH24	Hours, in 24 hours
MI	Minute
SS	Seconds
MM	Month
Mon	Abbreviated month
Month	Full name of month
W	The week of the month.
WW	The week of the year

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;

Related Articles]

• Update data rows using the Oracle 10g MERGE statement

• Summary of Oracle Database PL/SQL encoding rules

• Oracle recovery manager and DBMS_JOB package Analysis