Oracle 的默认 DATE 格式

第一次使用 Oracle SQL(我已经习惯了 MySQL).我发现有关默认日期格式的信息存在冲突.在多次尝试将 TO_DATE 与我的 INSERT INTO my_table 语句一起使用后，我终于找到了我使用的数据库期望 DD-MON-YY(即 25-JAN-18).然而，在 stackoverflow 和其他地方的各个页面上，我看到一些说默认是 YYYYMMDD 或 DD/MM/YYYY 或 YYYY-MM-DD.为什么会有这么多相互矛盾的信息?

解决方案

Oracle 和其他数据库一样，允许您设置默认格式.开箱即用的格式是(通常)DD-MON-RR，其中RR"是指两位数的年份.从歧义(两位数年份?)和国际化(哪些国家/地区实际上是默认设置?)的角度来看，这是一种非常糟糕的格式.但是 Oracle 已经存在了很长时间.

标准格式也由国际标准组织 ISO 定义.他们选择了更像 YYYY-MM-DD 的东西.实际上，连字符是可选的，但我认为它们使日期更具可读性.

Oracle 接受这种格式的常量，如果你使用 DATE:

选择日期'2018-01-25'

这很方便.首先，支持合理的标准很好.其次，无论国际化设置如何，代码都是安全的.Oracle 文档当然详细介绍了这一点；这里是一个起点.>

First time using Oracle SQL (I'm used to MySQL). I'm finding conflicting info on what the default date format is. After several attempts having to use TO_DATE with my INSERT INTO my_table statements, I finally found the database I'm using expects DD-MON-YY (i.e. 25-JAN-18). Yet on various pages here in stackoverflow and elsewhere, I see some that say default is YYYYMMDD or DD/MM/YYYY or YYYY-MM-DD. Why so many conflicting pieces of information?

解决方案

Oracle, as well as other databases, allows you to set the default format. Out of the box, the format is (typically) DD-MON-RR, where "RR" refers to a two-digit year. This is a pretty lousy format, from the perspective of ambiguity (two digit year?) and internationalization (for what countries is that actually the default?). But Oracle has been around a long, long time.

Standard formats are also defined by ISO, the International Standards Organization. They settled on something more like YYYY-MM-DD. Actually, the hyphens are optional, but I think they make the date much more readable.

Oracle accepts constants in this format, if you use DATE:

select DATE '2018-01-25'

This is very handy. First, it is nice to support reasonable standards. Second, the code is safe, regardless of internationalization settings. Oracle documentation of course covers this in detail; here is one place to start.