Describe
Execute the following SQL
selectTO_DATE(‘2018-1-9 12:41:00‘,‘yyyy-MM-dd hh24:mm:ss‘from dual;
And then I reported the following error.
ORA-01810: Format code appears two times
- 00000-"Format code appears twice"
Cause:
Action:
Select Receivedate,receivetime,to_date (concat (Receivedate,concat ('), receivetime), ' Yyyy-mm-dd hh24:mm:ss ') from Workflow_currentoperator where RequestID = Nodeid = 162;
Causes and Solutions
The syntax for the TO_DATE function is as follows:
TO_DATE(char,fmt);
Where Char is a string, and FMT is a date format, such as yyyy-mm-dd hh24:mi:ss
.
For SQL syntax is case-insensitive, so the date format of mm and MM is the same, in Oracle's date conversion for the month should be used MM
, for minutes should be used MI
. SQL should be modified to read as follows:
selectTO_DATE(‘2018-1-9 12:41:00‘,‘yyyy-MM-dd hh24:mi:ss‘from dual;
The time-date format for Oracle is the following table:
Elements |
Description |
Dd |
Days of the Month (1-31). |
YYYY |
Year |
Mm |
Month (01-12; January = 01). |
HH |
Time (12-hour system) |
HH12 |
Time (12-hour system) |
HH24 |
Time (24-hour system) |
MI |
Minutes |
Ss |
Seconds |
This is not the same as the Java time-date format, and the Java time-date format is the following table:
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");sdf.format(date);
| Letter
Date or Time Component |
Presentation |
Examples |
G |
Era designator |
Text |
AD |
y |
Year |
Year |
1996 ; 96 |
Y |
Week year |
Year |
2009 ; 09 |
M |
Month in year (context sensitive) |
Month |
July ; Jul ; 07 |
L |
Month in year (standalone form) |
Month |
July ; Jul ; 07 |
w |
Week in year |
Number |
27 |
W |
Week in month |
Number |
2 |
D |
Day in year |
Number |
189 |
d |
Day in month |
Number |
10 |
F |
Day's week in month |
Number |
2 |
E |
Day name in week |
Text |
Tuesday ; Tue |
u |
Day number of week (1 = Monday, ..., 7 = Sunday) |
Number |
1 |
a |
AM/PM Marker |
Text |
PM |
H |
Hour in Day (0-23) |
Number |
0 |
k |
Hour in Day (1-24) |
Number |
24 |
K |
Hour in AM/PM (0-11) |
Number |
0 |
h |
Hour in AM/PM (1-12) |
Number |
12 |
m |
Minute in Hour |
Number |
30 |
s |
Second in minute |
Number |
55 |
S |
Millisecond |
Number |
978 |
z |
Time zone |
General Time Zone |
Pacific Standard Time ; PST ; GMT-08:00 |
Z |
Time zone |
RFC 822 Time Zone |
-0800 |
X |
Time zone |
ISO 8601 time Zone |
-08 ; -0800 ;-08:00 |
Reference
1.https://docs.oracle.com/cd/e11882_01/server.112/e41084/functions203.htm#sqlrf06132
2.https://docs.oracle.com/cd/e11882_01/server.112/e41084/sql_elements004.htm#sqlrf00212
3.java.text.simpledateformat
Oracle Date conversion ORA-01810: Format code appears two times 01810. 00000-"Format code appears twice"