Oracle Date Processing

Reproduced:

To_date () with 24-hour notation and MM-minute display:

One, when using Oracle's to_date function to do date conversion, many Java programmers may use the "YYYY-MM-DD HH:mm:ss" format as a format for conversion, but in Oracle will cause an error: "ORA 01810 The format code appears two times.

1. Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mm:ss ') from dual;

Such as:
The reason is that SQL is case-insensitive, mm and mm are considered the same format code, so Oracle's SQL uses MI instead of minutes.

1. Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;

Second, the other to be in the form of 24 hours to use HH24
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mi:ss ') from Dual;//mi is minutes
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mm:ss ') from dual;//mm shows the meaning of the to_date parameter in Oracle for the month

1. Date Format parameter meaning description
D the week of the week
Day's name, padded to 9 characters using a space
Day of DD Month
The day of the DDD year
DY-Day Shorthand name
The first week of the year in the IW ISO standard
IYYY ISO standard four-bit year
YYYY four-bit year
The last three digits of the yyy,yy,y year, two bits, one
HH hours, in 12-hour meter
HH24 hours, by 24 hours
MI points
SS sec
MM Month
Shorthand for Mon Month
Full name of month
W the first few weeks of the month
The first few weeks of WW 1. Date-time interval operation
Time minus 7 minutes for the current time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual
The current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual
The current time minus 7 days
Select Sysdate-interval ' 7 ' Day from dual
Time minus July for current time
Select Sysdate,sysdate-interval ' 7 ' month from dual
Time minus 7 years in the current time
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 manipulation

1. Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual
2. Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh:mi:ss ') from dual
3. Select Sysdate,to_char (sysdate, ' yyyy-ddd hh:mi:ss ') from dual
4. Select Sysdate,to_char (sysdate, ' yyyy-mm iw-d hh:mi:ss ') from dual

Refer to the related documentation for Oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4. htm#48515)

3. Character to date operation

1. Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual

The concrete usage is similar to the to_char above.

4. Use of the Trunk/round function

1. Select Trunc (Sysdate, ' year ') from dual
2. Select Trunc (sysdate) from dual
3. Select To_char (trunc (sysdate, ' YYYY '), ' YYYY ') from dual

5.oracle data types with millisecond levels
--Returns the current time, month, day, hour, minute, millisecond
Select To_char (Current_timestamp (5), ' dd-mon-yyyy HH24:MI:SSxFF ') from dual;
--Returns the second millisecond of the current time, specifying the precision after seconds (Max =9)
Select To_char (Current_timestamp (9), ' Mi:ssxff ') from dual;

6. Calculate when the program is running (MS)

2. Declare
4. Type RC is REF CURSOR;
6. L_RC RC;
8. L_dummy All_objects.object_name%type;
10. L_start number default dbms_utility.get_time;
12. Begin
14. For I in 1.. 1000
16. Loop
18. Open L_RC for
20. ' Select object_name from all_objects ' | |
22. ' WHERE object_id = ' | | i;
24. Fetch L_RC into l_dummy;
26. Close L_RC;
28. End Loop;
30. Dbms_output.put_line
32. (Round ((dbms_utility.get_time-l_start)/100, 2) | |
34. ' seconds ... ');
36. End