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 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.
但说真的，为什么违约不高于违约呢?特别是在DBMS中，两个主要的时间跟踪数据类型(日期和时间戳)都有一个包含(至少)精确到1秒的时间组件。
11 个解决方案
#1
52
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;
#2
78
Are you sure you're not confusing Oracle database with Oracle SQL Developer?
您确定没有将Oracle数据库与Oracle SQL开发人员混淆吗?
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.
数据库本身没有日期格式，日期以原始形式从数据库中出来。它由客户端软件来呈现，而SQL开发人员使用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可以重新配置，以正确显示日期值，它只是有坏的默认值。
#3
20
The format YYYY-MM-DD is part of ISO8601 a standard for the exchange of date (and time) information.
YYYY-MM-DD格式是ISO8601标准的一部分，用于交换日期(和时间)信息。
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.
Oracle非常勇敢地采用了这样的ISO标准，但同时，奇怪的是他们并没有一直这样做。
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.
我知道我说的是革命性的东西，但是我们都应该接受ISO标准，即使是我们一次做一点。
#4
11
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的时候，你选择了Locales和install options的组合(非常明智的选择!)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 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 with
如果您希望您的SQL可以移植到另一个Oracle站点，那么我建议您总是将TO_CHAR(datecol, YYYY-MM-DD)或类似的函数打包在每个日期列上，您的SQL或可替换性在您连接之后立即设置defualt格式。
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
or similar.
或类似的。
#5
9
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.
依赖默认设置为特定值是不明智的，IMHO，无论是以日期格式、货币格式、优化模式还是其他方式。您应该始终在服务器、客户机或应用程序中设置所需的日期格式的值。
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，这让我抓狂，但至少迫使我总是使用显式转换。
#6
6
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:
如果您使用的是Oracle SQL Developer:
Tool > Preferences > Database > NLS
工具>偏好>数据库> NLS。
Date Format: YYYY-MM-DD HH24:MI:SS
日期格式:YYYY-MM-DD HH24:MI:党*
#7
5
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客户端还有其他缺省值。
#8
4
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标准的日期值为YYYY-MM-DD。因此，即使Oracle存储了时间信息，我的猜测是它们以与SQL标准兼容的方式显示值。在标准中，有时间戳数据类型，包括日期和时间信息。
#9
2
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.
在大多数数据库(以及精确的语言)中，日期不包括时间。有一个日期并不意味着你在那个日期是指某一秒。一般来说，如果你想要一个时间和日期，那就叫做时间戳。
#10
1
Oracle has both the Date and the Timestamp data types.
Oracle具有日期和时间戳数据类型。
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文档，日期和时间戳之间的数据大小有差异，所以当意图是只有一个日期字段时，显示日期格式是有意义的。而且，“它没有分数秒或时区。”-因此，当需要时间戳信息时，这不是最佳选择。
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 - Date查询中的时间组件在PL/SQL Developer中显示的时间，但不显示在Oracle SQL Developer中。但它不会显示分数秒或时区——为此您需要时间戳数据类型。
#11
1
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.
原因:如果您正在查看一个带有时间戳的数据列，那么最重要的信息就是年份。如果数据在数据库中已经存在了10年，这一点很重要。所以一年。
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.
如果要在默认情况下显示数据，那么应该只显示数据中最重要的部分，所以默认情况下，只有Y-M-D。其他所有内容都可以显示，但是默认情况下不会使sql报告混乱。
Ordering by date is logical if you display Y-M-D because it is sequential. Computers are good at sequential, and it looks logical.
如果显示Y-M-D，按日期排序是合理的，因为它是顺序的。计算机是按顺序排列的，而且看起来很符合逻辑。
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)
我是我们出生的，我不代表神谕。然而，我可以为自己想想
#1
52
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;
#2
78
Are you sure you're not confusing Oracle database with Oracle SQL Developer?
您确定没有将Oracle数据库与Oracle SQL开发人员混淆吗?
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.
数据库本身没有日期格式，日期以原始形式从数据库中出来。它由客户端软件来呈现，而SQL开发人员使用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可以重新配置，以正确显示日期值，它只是有坏的默认值。
#3
20
The format YYYY-MM-DD is part of ISO8601 a standard for the exchange of date (and time) information.
YYYY-MM-DD格式是ISO8601标准的一部分，用于交换日期(和时间)信息。
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.
Oracle非常勇敢地采用了这样的ISO标准，但同时，奇怪的是他们并没有一直这样做。
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.
我知道我说的是革命性的东西，但是我们都应该接受ISO标准，即使是我们一次做一点。
#4
11
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的时候，你选择了Locales和install options的组合(非常明智的选择!)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 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 with
如果您希望您的SQL可以移植到另一个Oracle站点，那么我建议您总是将TO_CHAR(datecol, YYYY-MM-DD)或类似的函数打包在每个日期列上，您的SQL或可替换性在您连接之后立即设置defualt格式。
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
or similar.
或类似的。
#5
9
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.
依赖默认设置为特定值是不明智的，IMHO，无论是以日期格式、货币格式、优化模式还是其他方式。您应该始终在服务器、客户机或应用程序中设置所需的日期格式的值。
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，这让我抓狂，但至少迫使我总是使用显式转换。
#6
6
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:
如果您使用的是Oracle SQL Developer:
Tool > Preferences > Database > NLS
工具>偏好>数据库> NLS。
Date Format: YYYY-MM-DD HH24:MI:SS
日期格式:YYYY-MM-DD HH24:MI:党*
#7
5
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客户端还有其他缺省值。
#8
4
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标准的日期值为YYYY-MM-DD。因此，即使Oracle存储了时间信息，我的猜测是它们以与SQL标准兼容的方式显示值。在标准中，有时间戳数据类型，包括日期和时间信息。
#9
2
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.
在大多数数据库(以及精确的语言)中，日期不包括时间。有一个日期并不意味着你在那个日期是指某一秒。一般来说，如果你想要一个时间和日期，那就叫做时间戳。
#10
1
Oracle has both the Date and the Timestamp data types.
Oracle具有日期和时间戳数据类型。
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文档，日期和时间戳之间的数据大小有差异，所以当意图是只有一个日期字段时，显示日期格式是有意义的。而且，“它没有分数秒或时区。”-因此，当需要时间戳信息时，这不是最佳选择。
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 - Date查询中的时间组件在PL/SQL Developer中显示的时间，但不显示在Oracle SQL Developer中。但它不会显示分数秒或时区——为此您需要时间戳数据类型。
#11
1
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.
原因:如果您正在查看一个带有时间戳的数据列，那么最重要的信息就是年份。如果数据在数据库中已经存在了10年，这一点很重要。所以一年。
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.
如果要在默认情况下显示数据，那么应该只显示数据中最重要的部分，所以默认情况下，只有Y-M-D。其他所有内容都可以显示，但是默认情况下不会使sql报告混乱。
Ordering by date is logical if you display Y-M-D because it is sequential. Computers are good at sequential, and it looks logical.
如果显示Y-M-D，按日期排序是合理的，因为它是顺序的。计算机是按顺序排列的，而且看起来很符合逻辑。
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)
我是我们出生的，我不代表神谕。然而，我可以为自己想想