oracle 格式化日期
我在包含 dd-MMM-yy 格式的日期的表中有一个日期字段.
I have a date field in a table that contains date in dd-MMM-yy format .
我想创建一个函数，让这个日期检查它是否为空，然后将其更改为 yyyy/mm/dd 格式
I want to create a function that get this date check it for not null and then change it to yyyy/mm/dd format
但问题是 oracle 不接受 dd-MMM-yy 格式日期作为输入参数和它说:请使用 yyyy-MM-dd 日期格式！！！
but the problem is that oracle doesn't accept dd-MMM-yy formate date as a input parameter and it say : Please Use yyyy-MM-dd date format !!!
我如何首先将 dd-MMM-yy 格式更改为 yyyy-MM-dd，以便 oracle 接受它作为输入然后将其更改为 yyyy/mm/dd
how can I first change dd-MMM-yy formate to yyyy-MM-dd so oracle accept it as input then change it to yyyy/mm/dd
推荐答案
: 请使用 yyyy-MM-dd 日期格式！！！
: Please Use yyyy-MM-dd date format !!!
这与 Oracle 错误无关.
That is no way related to an Oracle error.
我在包含 dd-MMM-yy 格式的日期的表中有一个日期字段.
I have a date field in a table that contains date in dd-MMM-yy format .
不，你很困惑.Oracle 不会以您看到的格式存储日期.它在内部以 7 个字节存储它，每个字节存储日期时间值的不同组成部分.
No, you are confused. Oracle does not store dates in the format you see. It stores it internally in 7 bytes with each byte storing different components of the datetime value.
Byte Description
---- -------------------------------------------------
1 Century value but before storing it add 100 to it
2 Year and 100 is added to it before storing
3 Month
4 Day of the month
5 Hours but add 1 before storing it
6 Minutes but add 1 before storing it
7 Seconds but add 1 before storing it
如果要显示，请使用TO_CHAR和适当的FORMAT MODEL.
If you want to display, use TO_CHAR with proper FORMAT MODEL.
插入时，使用TO_DATE和适当的FORMAT MODEL.
While inserting, use TO_DATE with proper FORMAT MODEL.
默认情况下，您看到的格式是您的特定于区域设置的 NLS 设置.
What you see as a format by default, is your locale specific NLS settings.
SQL> select parameter, value from v$nls_parameters where parameter='NLS_DATE_FORMAT';
PARAMETER VALUE
--------------- ----------------------------------------------------------------
NLS_DATE_FORMAT DD-MON-RR
SQL> select sysdate from dual;
SYSDATE
---------
03-FEB-15
SQL> select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'MM
-------------------
02/03/2015 17:59:42
SQL>
更新关于MMM格式.
对于 MMM，如果您指的是最多三个字符的月份名称，请使用 MON.
By MMM if you mean the month name up to three characters, then use MON.
相关文章