Oracle 的默认日期格式是 YYYY-MM-DD，为什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1837974/
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
Oracle's default date format is YYYY-MM-DD, WHY?
提问by Aaron Fi
Oracle's default date format is YYYY-MM-DD. Which means if I do:
Oracle 的默认日期格式是 YYYY-MM-DD。这意味着如果我这样做：
select some_date from some_table
...I losethe time portion of my date.
...我失去了约会的时间部分。
Yes, I know you can "fix" this with:
是的，我知道您可以通过以下方式“修复”此问题：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
But seriously, whyisn't the above the default? Especially in a DBMS where the two primary time-tracking data types (DATE and TIMESTAMP) bothhave a time component that includes (at least) accuracy down to 1 second.
但说真的，为什么上面不是默认值？尤其是在DBMS其中两个初级时间跟踪数据类型（DATE和时间戳）都具有包括（至少）一个时间分量精度达到1秒。
采纳答案by Doug Porter
If you are using this query to generate an input file for your Data Warehouse, then you need to format the data appropriately. Essentially in that case you are converting the date (which does have a time component) to a string. You need to explicitly format your string or change your nls_date_format to set the default. In your query you could simply do:
如果您使用此查询为您的数据仓库生成输入文件，那么您需要适当地格式化数据。基本上在这种情况下，您将日期（确实有时间组件）转换为字符串。您需要明确格式化您的字符串或更改您的 nls_date_format 以设置默认值。在您的查询中，您可以简单地执行以下操作：
select to_char(some_date, 'yyyy-mm-dd hh24:mi:ss') my_date
from some_table;
回答by skaffman
Are you sure you're not confusing Oracle database with Oracle SQL Developer?
您确定不会将 Oracle 数据库与 Oracle SQL Developer 混淆吗？
The database itself has no date format, the date comes out of the database in raw form. It's up to the client software to render it, and SQL Developer doesuse YYYY-MM-DD as its default format, which is next to useless, I agree.
数据库本身没有日期格式，日期以原始形式从数据库中出来。由客户端软件来呈现它，SQL Developer确实使用 YYYY-MM-DD 作为其默认格式，这几乎是无用的，我同意。
edit: As was commented below, SQL Developer can be reconfigured to display DATE values properly, it just has bad defaults.
编辑：正如下面所评论的，可以重新配置 SQL Developer 以正确显示 DATE 值，它只是具有错误的默认值。
回答by pavium
The format YYYY-MM-DD is part of ISO8601a standard for the exchange of date (and time) information.
格式 YYYY-MM-DD 是ISO8601标准的一部分，用于交换日期（和时间）信息。
It's very brave of Oracle to adopt an ISOstandard like this, but at the same time, strange they didn't go all the way.
Oracle 采用这样的ISO标准是非常勇敢的，但同时，奇怪的是他们并没有一路走下去。
In general people resist anything different, but there are many good Internationalreasonsfor it.
一般来说，人们抵制任何不同的东西，但有很多很好的国际理由。
I know I'm saying revolutionary things, but we should all embrace ISO standards, even it we do it a bit at a time.
我知道我在说革命性的东西，但我们都应该怀抱ISO标准，甚至是我们做这一点在同一时间。
回答by James Anderson
The biggest PITA of Oracle is that is does not have a default date format!
Oracle 最大的 PITA 就是没有默认的日期格式！
In your installation of Oracle the combination of Locales and install options has picked (the very sensible!) YYYY-MM-DD as the format for outputting dates. Another installation could have picked "DD/MM/YYYY" or "YYYY/DD/MM".
在您的 Oracle 安装中，区域设置和安装选项的组合选择（非常明智！）YYYY-MM-DD 作为输出日期的格式。另一个安装可能选择了“DD/MM/YYYY”或“YYYY/DD/MM”。
If you want your SQL to be portable to another Oracle site I would recommend you alwayswrap a TO_CHAR(datecol,'YYYY-MM-DD')or similar function around each date column your SQL or alternativly set the defualt format immediatly after you connect with
如果您希望您的 SQL 可移植到另一个 Oracle 站点，我建议您始终TO_CHAR(datecol,'YYYY-MM-DD')在 SQL 的每个日期列周围 包装一个或类似的函数，或者在您连接后立即设置默认格式
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
or similar.
或类似。
回答by David Aldridge
It's never wise to rely on defaults being set to a particular value, IMHO, whether it's for date formats, currency formats, optimiser modes or whatever. You should always set the value of date format that you need, in the server, the client, or the application.
将默认值设置为特定值，恕我直言，无论是日期格式、货币格式、优化器模式还是其他任何内容，都永远不会明智。您应该始终在服务器、客户端或应用程序中设置您需要的日期格式的值。
In particular, never rely on defaults when converting date or numeric data types for display purposes, because a single change to the database can break your application. Always use an explicit conversion format. For years I worked on Oracle systems where the out of the box default date display format was MM/DD/RR, which drove me nuts but at least forced me to always use an explicit conversion.
尤其是，在为显示目的而转换日期或数字数据类型时，永远不要依赖默认值，因为对数据库的一次更改可能会破坏您的应用程序。始终使用显式转换格式。多年来，我在 Oracle 系统上工作，其中开箱即用的默认日期显示格式为 MM/DD/RR，这让我很抓狂，但至少迫使我始终使用显式转换。
回答by Fabio Farath
Most of the IDE you can configure the default mask for some kind of data as date, currency, decimal separator, etc.
大多数 IDE 可以为某种数据配置默认掩码，如日期、货币、小数点分隔符等。
If your are using Oracle SQL Developer:
如果您使用的是Oracle SQL Developer：
Tool > Preferences > Database > NLS
工具 > 首选项 > 数据库 > NLS
Date Format: YYYY-MM-DD HH24:MI:SS
日期格式：YYYY-MM-DD HH24:MI:SS
回答by Harold L
A DATE value per the SQL standard is YYYY-MM-DD. So even though Oracle stores the time information, my guess is that they're displaying the value in a way that is compatible with the SQL standard. In the standard, there is the TIMESTAMP data type that includes date and time info.
每个 SQL 标准的 DATE 值是 YYYY-MM-DD。因此，即使 Oracle 存储时间信息，我的猜测是它们以与 SQL 标准兼容的方式显示值。在标准中，有包含日期和时间信息的 TIMESTAMP 数据类型。
回答by a_horse_with_no_name
This is a "problem" on the client side, not really an Oracle problem.
这是客户端的“问题”，而不是真正的 Oracle 问题。
It's the client application which formats and displays the date this way.
客户端应用程序以这种方式格式化和显示日期。
In your case it's SQL*Plus which does this formatting.
Other SQL clients have other defaults.
在您的情况下，它是执行此格式的 SQL*Plus。
其他 SQL 客户端有其他默认值。
回答by Nick Bastin
I'm not an Oracle user (well, lately anyhow), BUT...
我不是 Oracle 用户（好吧，最近无论如何），但是...
In most databases (and in precise language), a date doesn't include a time. Having a date doesn't imply that you are denoting a specific second on that date. Generally if you want a time as well as a date, that's called a timestamp.
在大多数数据库（和精确的语言）中，日期不包括时间。有日期并不意味着您在该日期表示特定的秒。通常，如果您想要时间和日期，这称为时间戳。
回答by razvanone
Oracle has both the Date and the Timestamp data types.
Oracle 有 Date 和 Timestamp 两种数据类型。
According to Oracle documentation, there are differences in data size between Date and Timestamp, so when the intention is to have a Date only field it makes sense to show the Date formatting. Also, "It does not have fractional seconds or a time zone." - so it is not the best choice when timestamp information is required.
根据Oracle 文档，Date 和 Timestamp 之间的数据大小存在差异，因此当打算使用 Date only 字段时，显示日期格式是有意义的。此外，“它没有小数秒或时区。” - 所以当需要时间戳信息时它不是最佳选择。
The Date field can be easily formatted to show the time component in the Oracle SQL Developer - Date query ran in PL/SQL Developer shows time, but does not show in Oracle SQL Developer. But it won't show the fractional seconds or the time zone - for this you need Timestamp data type.
日期字段可以轻松格式化以显示 Oracle SQL Developer 中的时间组件 -在 PL/SQL Developer 中运行的日期查询显示时间，但在 Oracle SQL Developer 中不显示。但它不会显示小数秒或时区 - 为此您需要 Timestamp 数据类型。
