Conversion of date formats in Oracle

 

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

For example, select to_date ('1970-01-01 13:14:20 ', 'yyyy-mm-dd hh24: mm: ss') from dual;

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