ORA-01843: 在 oracle 中插入日期时不是有效月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14137896/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01843: not a valid month when insert a date in oracle
提问by Atomico
I'm trying to insert a row in oracle database and when i try with the date i have the errore in title.
My date is DD/MM/YYYY HH:MM:SS(example 25/01/2013 12:07:19)
我正在尝试在 oracle 数据库中插入一行，当我尝试使用日期时，标题中有错误。我的日期是DD/MM/YYYY HH:MM:SS（示例25/01/2013 12:07:19）
edit for better explain:
i have an android application and want to insert a row in oracle database by php.
in Php i have:
编辑以便更好地解释：我有一个 android 应用程序，想通过 php 在 oracle 数据库中插入一行。
在 PHP 我有：
$sqlString = sprintf("INSERT INTO GUASTI (%s, %s, %s, %s, %s, %s)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s')"
,'guasto','tipo','data', 'localita', 'indirizzo_id', 'utente_id',
$array['guasto'], $array['tipo'], $array['data'], $array['localita'], $idUtenti, $idIndirizzo);
where $array['data']is 25/01/2013 12:07:19
这里$array['data']是25/01/2013 12:07:19
p.s. i know that there are security problems there but it is not a problem for now.
ps 我知道那里有安全问题，但现在不是问题。
回答by Matteo
MMis for month. Use MIfor minutes.
MM是一个月。使用MI几分钟。
You have
你有
HH:MM:SS
every time where the minutes are greater than 12 will trigger the error as you are telling Oracle to interpret them as months.
每次分钟大于 12 时都会触发错误，因为您告诉 Oracle 将它们解释为月份。
You are also using HH without am/pm (in your example you just used 12). If you are using a 24 format use HH24
您还使用没有 am/pm 的 HH（在您刚刚使用的示例中12）。如果您使用的是 24 格式，请使用HH24
DD/MM/YYYY HH24:MI:SS
or if you want the 12-hour format
或者如果你想要 12 小时格式
DD/MM/YYYY HH:MI:SSAM
and then
进而
02/01/2013 07:42:00am
Edit
编辑
You are inserting the date with the default format which is MM/DD/YYYY (american standard): 25 is not a valid month. You can use the TO_DATEfunction
您使用默认格式插入日期，即 MM/DD/YYYY（美国标准）：25 不是有效月份。您可以使用该TO_DATE功能
'TO_DATE(' . $array['data'] . ', DD/MM/YYYY HH24:MI:SS)'
回答by Sonal Patil
Use TO_DATE('20/08/2012 09:00:00','DD/MM/YYYY HH24:MI:SS') while inserting date for more details see link Oracle Error
在插入日期时使用 TO_DATE('20/08/2012 09:00:00','DD/MM/YYYY HH24:MI:SS') 以获取更多详细信息，请参阅链接Oracle 错误
回答by Jyoti Prakash
Just for more info:
只是为了更多信息：
The following pattern letters are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved):
定义了以下模式字母（从“A”到“Z”以及从“a”到“z”的所有其他字符都被保留）：
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 of 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
