Oracle的默认DATE格式 [英] Oracle's default DATE format

问题描述

第一次使用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"表示两位数的年份.从歧义(两位数年份?)和国际化(实际上是默认的国家/地区)的角度来看，这是一种非常糟糕的格式.但是甲骨文已经存在了很长时间了.

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

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

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

这篇关于Oracle的默认DATE格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！