Oracle默认的日期格式是YYYY-MM-DD，为什么呢？
Oracle 的默认日期格式是 YYYY-MM-DD。这意味着如果我这样做：
select some_date from some_table
...我失去约会的时间部分。
是的，我知道你可以用以下方法“修复”这个问题：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
但是说真的，为什么上面不是默认的？特别是在 DBMS 中，两种主要时间跟踪数据类型（DATE 和 TIMESTAMP）都都具有包含（至少）低至 1 秒的精度的时间组件。
Oracle's default date format is YYYY-MM-DD. Which means if I do:
select some_date from some_table
...I lose the 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, why isn't the above the default? Especially in a DBMS where the two primary time-tracking data types (DATE and TIMESTAMP) both have a time component that includes (at least) accuracy down to 1 second.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（13）
您确定没有将 Oracle 数据库与 Oracle SQL Developer 混淆吗？
数据库本身没有日期格式，日期以原始形式从数据库中出来。由客户端软件来呈现它，并且 SQL Developer 确实使用 YYYY-MM-DD 作为其默认格式，我同意，这几乎没有用处。
编辑：正如下面评论的，SQL Developer 可以重新配置以正确显示 DATE 值，它只是有错误的默认值。
Are you sure you're not confusing Oracle database with 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 does use YYYY-MM-DD as its default format, which is next to useless, I agree.
edit: As was commented below, SQL Developer can be reconfigured to display DATE values properly, it just has bad defaults.
如果您使用此查询为数据仓库生成输入文件，则需要适当地格式化数据。本质上，在这种情况下，您将日期（确实有时间部分）转换为字符串。您需要显式格式化字符串或更改 nls_date_format 以设置默认值。在您的查询中您可以简单地执行以下操作：
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:
YYYY-MM-DD 格式是 ISO8601 日期（和时间）交换标准的一部分） 信息。
Oracle 采用这样的ISO 标准是非常勇敢的，但同时，奇怪的是他们并没有一路走下去。
一般来说，人们抵制任何不同的东西，但是有很多好的国际原因。
我知道我说的是革命性的事情，但我们都应该接受 ISO 标准，即使我们一次做一点。
The format YYYY-MM-DD is part of ISO8601 a standard for the exchange of date (and time) information.
It's very brave of Oracle to adopt an ISO standard like this, but at the same time, strange they didn't go all the way.
In general people resist anything different, but there are many good International reasons for 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.
Oracle最大的PITA是它没有默认的日期格式！
在 Oracle 安装中，区域设置和安装选项的组合选择了（非常明智！）YYYY-MM-DD 作为输出日期的格式。另一个安装可能选择“DD/MM/YYYY”或“YYYY/DD/MM”。
如果您希望您的 SQL 可移植到另一个 Oracle 站点，我建议您始终在每个 SQL 周围包装一个
TO_CHAR(datecol,'YYYY-MM-DD')或类似函数SQL 的日期列，或者在连接后立即设置默认格式或类似的格式。
The biggest PITA of Oracle is that is does not have a default date format!
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".
If you want your SQL to be portable to another Oracle site I would recommend you always wrap 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 withor similar.
恕我直言，无论是日期格式、货币格式、优化器模式还是其他什么，依赖设置为特定值的默认值都是不明智的。您应该始终在服务器、客户端或应用程序中设置所需的日期格式值。
特别是，在出于显示目的转换日期或数字数据类型时，切勿依赖默认值，因为对数据库的单个更改可能会破坏您的应用程序。始终使用显式转换格式。多年来，我在 Oracle 系统上工作，其中开箱即用的默认日期显示格式是 MM/DD/RR，这让我抓狂，但至少迫使我始终使用显式转换。
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.
大多数 IDE 都可以为某种数据配置默认掩码，如日期、货币、小数分隔符等。
如果您使用的是 Oracle SQL Developer：
工具 >首选项>数据库> NLS
日期格式：YYYY-MM-DD HH24:MI:SS
Most of the IDE you can configure the default mask for some kind of data as date, currency, decimal separator, etc.
If your are using Oracle SQL Developer:
Tool > Preferences > Database > NLS
Date Format: YYYY-MM-DD HH24:MI:SS
根据 SQL 标准，DATE 值是 YYYY-MM-DD。因此，即使 Oracle 存储时间信息，我的猜测是它们以与 SQL 标准兼容的方式显示该值。在标准中，有包含日期和时间信息的 TIMESTAMP 数据类型。
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.
这是客户端的“问题”，而不是真正的 Oracle 问题。
客户端应用程序以这种方式格式化和显示日期。
在您的情况下，是 SQL*Plus 执行此格式化操作。
其他 SQL 客户端有其他默认值。
This is a "problem" on the client side, not really an Oracle problem.
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.
我不是 Oracle 用户（无论如何，最近），但是......
在大多数数据库（并且用精确的语言）中，日期不包含时间。指定日期并不意味着您要指定该日期的特定秒数。通常，如果您想要时间和日期，则称为时间戳。
I'm not an Oracle user (well, lately anyhow), BUT...
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.
Oracle 具有日期和时间戳两种数据类型。
根据 Oracle文档，日期和时间戳之间的数据大小存在差异，因此当目的是仅使用日期字段时，显示日期格式是有意义的。另外，“它没有小数秒或时区。” - 所以当需要时间戳信息时它不是最佳选择。
日期字段可以轻松格式化以在 Oracle SQL Developer 中显示时间部分 - 在 PL/SQL Developer 中运行的日期查询显示时间，但在 Oracle SQL Developer 中不显示。但它不会显示小数秒或时区 - 为此您需要时间戳数据类型。
Oracle has both the Date and the Timestamp data types.
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.
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.
原因：
如果您正在查看带有时间戳的一列数据，则最重要的信息位是年份。如果数据已经在数据库中保存了十年，那么了解这一点很重要。所以年是第一位的。
接下来是月份，然后是日、小时、分钟，这是有道理的。客观地说，这些是时间数据显示的最合乎逻辑的顺序。
如果您要默认显示数据，则应该仅显示数据的最重要部分，因此默认情况下仅显示 YMD，这也是有意义的。其他所有内容都可以显示，但默认情况下它不会扰乱您的 sql 报告。
如果显示 YMD，则按日期排序是合乎逻辑的，因为它是连续的。计算机擅长顺序，而且看起来很合乎逻辑。
最后。你想要 MDY 的偏见就是你的偏见。即使在美国，也不是每个人都使用这种格式。因此，请使用最符合逻辑的格式，当其他人决定默认采用符合逻辑的格式时，不要感到愤怒。
（我是美国出生的，我不代表甲骨文。不过，我可以自己思考）
reason:
if you are looking at a column of data with a time stamp, the _MOST_IMPORTANT_ bit of information is the year. If data has been in the db for ten years, that's important to know. so year comes first.
it makes sense that month would come next, then day, hour, minute. Objectively, these are the most logical sequence for time data to be displayed.
it also makes sense that if you are going to display the data by default, you should display only the most significant portion of the data, so by default, only Y-M-D. everything else CAN be displayed, but it does not clutter your sql report by default.
Ordering by date is logical if you display Y-M-D because it is sequential. Computers are good at sequential, and it looks logical.
finally. Your bias to want M-D-Y is your bias. Not everyone even in the US uses this format. So use the most logical format and don't be outraged when others decide to be logical by default.
(I am US born, and I do not represent Oracle. I can, however, think for myself)
这个问题是基于一种常见的误解，即 DATE 数据类型在数据库中有一种格式；事实并非如此。
在 Oracle 中，
DATE是一种二进制数据类型，由 7 个字节组成，分别表示：世纪、世纪年份、月、日、小时、分钟和秒。它始终具有这 7 个组件，并且从不以任何特定的人类可读格式存储。Oracle 的
DATE数据类型早于 ANSI 标准（大约 7 年），并且保持了向后兼容性与以前的数据库版本。如前所述，它不是，它是一个没有格式的 7 字节二进制值。当客户端应用程序（即 SQL*Plus、SQL Developer、Java、C# 等）请求
DATE时，数据库将向该客户端发送 7 字节的DATE值;但是，未格式化的二进制数据对您（用户）来说并不是特别有用，因此客户端应用程序选择如何格式化日期，以便它对您（用户）有意义，这意味着它必须将其从二进制转换为格式化值。这意味着
DATE的任何隐式格式化均由客户端应用程序完成，而不是由数据库完成。数据库在处理日期时将始终使用原始字节。这取决于您使用的客户端应用程序。但是，在大多数情况下，您可以更改客户端应用程序的首选项并为
DATE设置您自己的格式模型。您需要参考该客户端应用程序的文档（因为有许多不同的客户端，并且它们都有不同的方式来设置默认的DATE格式）。这并不完全正确。
YYYY-MM-DD作为格式模型，而是默认使用数据库的NLS_DATE_FORMAT作为客户端显示日期的格式模型（尽管它们可以被覆盖）在客户端配置中）。NLS_DATE_FORMAT是 Oracle 将用于隐式转换字符串到日期和日期到字符串的格式（即，如果您使用TO_DATE(string_column)或TO_CHAR(date_column)如果没有在第二个参数中指定格式模型，那么它将使用NLS_DATE_FORMAT)。但是，值得重复的是，当客户端应用程序请求DATE时，数据库不会对其保存的二进制数据执行任何修改，而只是向客户端发送 7 字节值，并让客户端应用程序对整个DATE（包括时间部分）做任何它想做的事情。NLS_DATE_FORMAT是 基于NLS_TERRITORY参数，因此是默认值NLS_DATE_FORMAT世界各地各不相同；默认情况下，当区域设置为保加利亚或瑞典时，数据库实例仅具有RRRR-MM-DD格式（但是，DBA 可以在数据库创建期间覆盖默认值）。RRRR-MM-DD并将其设置为默认值为您的领土；或者您的 DBA 已将默认值设置为YYYY-MM-DD（可能是因为它是格式化日期的 ISO8601 标准）。因此，您的问题的答案是：
NLS_DATE_FORMAT会话参数隐式地将格式应用于日期，并且设置了NLS_DATE_FORMAT要么隐式地基于数据库创建期间选择的NLS_TERRITORY，要么显式地由 DBA 选择。DATE的方式，请更改默认设置（在客户端上，或者如果客户端从数据库获取默认设置，则更改数据库）。The question is based on a common misconception that a
DATEdata-type has a format in the database; it does not.In Oracle, a
DATEis a binary data-type consisting of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 components and it is NEVER stored in any particular human-readable format.Oracle's
DATEdata type predates the ANSI standard (by about 7 years) and has maintained backwards compatibility with previous database versions.As stated, it is not, it is a 7-byte binary value without a format. When the client application (i.e. SQL*Plus, SQL Developer, Java, C#, etc.) requests a
DATEthen the database will send that client the 7-byteDATEvalue; however, unformatted binary data is not particularly useful to you, the user, so the client application chooses how to format the date so that it is meaningful to you, the user, and that means it has to convert it from binary to a formatted value.This means that any implicit formatting of a
DATEis done by the client application and NOT by the database. The database will ALWAYS work with the raw bytes when it is handling dates.This depends on the client application you are using. However, in most cases, you can change the preferences for the client application and set your own format model for
DATEs. You will need to refer to the documentation for that client application (as there are many different clients and they will all have a different way to set the defaultDATEformat).This is not entirely true.
YYYY-MM-DDas the format model but instead default to use the database'sNLS_DATE_FORMATas the client's format model for displaying dates (although they can be overridden with in the client-side configuration).NLS_DATE_FORMATis the format that Oracle will use to implicitly cast strings-to-dates and dates-to-strings (i.e. if you useTO_DATE(string_column)orTO_CHAR(date_column)without specifying a format model in the second argument then it will use theNLS_DATE_FORMAT). However, it is worth repeating that when a client application requests aDATEthen the database does not perform any modifications to the binary data it holds and just sends the client the 7-byte value and lets the client application do whatever it wants with the entireDATE(including the time component).NLS_DATE_FORMATis based on theNLS_TERRITORYparameter and therefore the defaultNLS_DATE_FORMATvaries throughout the world; out-of-the-box, a database instance only has the formatRRRR-MM-DDwhen the territory is set to Bulgaria or Sweden (however, the DBA can override the defaults during database creation).RRRR-MM-DDand set that as the default for your territory; or that your DBA has set the default toYYYY-MM-DD(possibly because it is the ISO8601 standard for formatting a date).So, the answer to your question is:
NLS_DATE_FORMATsession parameter and theNLS_DATE_FORMATwas set either implicitly based on theNLS_TERRITORYchosen during database creation or explicitly by the DBA.DATEthen change the default settings (either on the client or, if the client takes the default from the database then change the database).因为 Oracle 尚未像 Microsoft
Sql Server那样实现DateTime或SmallDateTime！但问题的正确答案不是定义 FIX 默认格式，而是定义 SIGNIFICANT 默认格式，该格式仅显示有效数字，以便显示的 DATE、TIME 或 DATETIME 值（默认情况下）始终包含所有重要数字。
示例：
原理很简单。
DATE 中的所有数字将始终显示为浮点数的 INTEGER 部分。对于 TIME 部分，与浮点数中的 DECIMAL 部分一样，仅显示有效部分。
当然，对于 TIME 类型（仅 HH:MM:SS），DATE 部分永远不会显示。
要在 Oracle 上显示 DATE+TIME，您必须定义 DATE 类型；但这不是 SQL 标准。
Because Oracle has not yet implemented
DateTimeorSmallDateTimeas MicrosoftSql Server!But the correct answer to your problem is not to define a FIX default format but a SIGNIFICANT default format that display only significant digits so that DATE, TIME or DATETIME values displayed (by default) contains always all important digits.
Example:
The principle is simple.
All digits being part of DATE will always be displayed as INTEGER part of float number. For TIME part, only significant part will be displayed as for DECIMAL part in float number.
Naturally, for TIME type (only HH:MM:SS), the DATE part is never displayed.
To display DATE+TIME on Oracle your must define a DATE type; but this is not a SQL standard.