การใช้ to_date และความหมาย Oracle to_date parameter meaning

Note the meaning of the date format parameter

D the week of the week

DAY-day name, padded with spaces to 9 characters

The first few days in mid-DD

In the first few days in the DDD

DY-day short name

IW ISO standard in the first few weeks

IYYY ISO standard four-year

Four Year YYYY

YYY, YY, Y the last three years, two, one

HH hour 12-hour meter

HH24-hour meter by 24-hour

MI points

SS seconds

MM month

Mon shorthand for the month

Month full name of the month

W the first few weeks of the month

WW in the first few weeks

1. Date Time interval operation

The current time minus 7 minutes

select sysdate, sysdate - interval '7 'MINUTE from dual

The current time minus 7 hours time

select sysdate - interval '7 'hour from dual

The current time minus 7 days

select sysdate - interval '7 'day from dual

The current time minus the time in July

select sysdate, sysdate - interval '7 'month from dual

The current time minus 7 years

select sysdate, sysdate - interval '7 'year from dual

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

Reference to the relevant customs documentation oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM # 48515)

3. Characters to the date of operation

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

Specific use and above to_char almost.

4. Trunk / ROUND function is used

select trunc (sysdate, 'YEAR') from dual select trunc (sysdate) from dual select to_char (trunc (sysdate, 'YYYY'), 'YYYY') from dual

5.oracle a millisecond-level data types

- Returns the current time, day hours minutes and seconds years milliseconds select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual; - returns the current time, seconds, milliseconds, seconds can be specified back accuracy (maximum = 9) select to_char (current_timestamp (9), 'MI: SSxFF') from dual;

6. Computer 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;