如何在 Oracle SQL Developer 中设置自定义日期时间格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8134493/
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
How can I set a custom date time format in Oracle SQL Developer?
提问by ComfortablyNumb
By default, Oracle SQL developer displays date values as 15-NOV-11. I would like to see the time part (hour/minute/second) by default.
默认情况下，Oracle SQL 开发人员将日期值显示为15-NOV-11. 我想默认查看时间部分（小时/分钟/秒）。
Is there a way to configure this within Oracle SQL Developer?
有没有办法在 Oracle SQL Developer 中配置它？
回答by ousoo
You can change this in preferences:
您可以在首选项中更改此设置：
- From Oracle SQL Developer's menu go to: Tools> Preferences.
- From the Preferencesdialog, select Database> NLSfrom the left panel.
- From the list of NLS parameters, enter
DD-MON-RR HH24:MI:SSinto the Date Formatfield. - Save and close the dialog, done!
- 从 Oracle SQL Developer 的菜单转到：Tools> Preferences。
- 在首选项对话框中，从左侧面板中选择数据库> NLS。
- 从NLS参数列表，进入
DD-MON-RR HH24:MI:SS到日期格式字段。 - 保存并关闭对话框，完成！
Here is a screenshot:
这是一个屏幕截图：
回答by hipsandy
I stumbled on this post while trying to change the display format for dates in sql-developer. Just wanted to add to this what I found out:
我在尝试更改 sql-developer 中日期的显示格式时偶然发现了这篇文章。只是想补充一下我发现的内容：
- To Change the default display format, I would use the steps provided by ousoo i.e Tools > Preferences > ...
But a lot of times, I just want to retain the DEFAULT_FORMAT while modifying the format only during a bunch of related queries. That's when I would change the format of the session with the following:
alter SESSION set NLS_DATE_FORMAT = 'my_required_date_format'
- 要更改默认显示格式，我将使用 ousoo 提供的步骤，即工具 > 首选项 > ...
但是很多时候，我只想保留 DEFAULT_FORMAT ，同时仅在一堆相关查询期间修改格式。那时我将使用以下内容更改会话的格式：
alter SESSION set NLS_DATE_FORMAT = 'my_required_date_format'
Eg:
例如：
alter SESSION set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'
回答by Donatello
With Oracle SQL Developer 3.2.20.09, i managed to set the custom format for the type DATE this way :
使用 Oracle SQL Developer 3.2.20.09，我设法以这种方式设置 DATE 类型的自定义格式：
In : Tools > Preferences > Database > NLS
在：工具 > 首选项 > 数据库 > NLS
Or : Outils > Préférences > Base de donées > NLS
或 : Outils > Préférences > Base de donées > NLS
YYYY-MM-DD HH24:MI:SS
YYYY-MM-DD HH24:MI:SS
Note that the following format does not workedfor me :
请注意，以下格式对我不起作用：
DD-MON-RR HH24:MI:SS
DD-MON-RR HH24:MI:SS
As a result, it keeps the default format, without any error.
因此，它保持默认格式，没有任何错误。
回答by Jay Dorsey
For anyone still having an issue with this; I happened to find this page from Google...
对于仍然有此问题的任何人；我碰巧从谷歌找到了这个页面......
Put it in like this (NLS Parameters)... it sticks for me in SQLDeveloper v3.0.04:
像这样输入（NLS 参数）...它在 SQLDeveloper v3.0.04 中对我有用：
DD-MON-YY HH12:MI:SS AM or for 24-Hour, DD-MON-YY HH24:MI:SS
回答by Kapu?niak
In my case the format set in Preferences/Database/NLS was [Date Format] = RRRR-MM-DD HH24:MI:SSXFF but in grid there were seen 8probably default format RRRR/MM/DD (even without time) The format has changed after changing the setting [Date Format] to: RRRR-MM-DD HH24:MI:SS (without 'XFF' at the end).
在我的情况下，在 Preferences/Database/NLS 中设置的格式是 [Date Format] = RRRR-MM-DD HH24:MI:SSXFF 但在网格中有 8probably 默认格式 RRRR/MM/DD（即使没有时间）格式有将设置 [Date Format] 更改为：RRRR-MM-DD HH24:MI:SS（末尾没有“XFF”）后更改。
There were no errors, but format with xff at the end didn't work.
没有错误，但最后用 xff 格式化不起作用。
Note: in polish notation RRRR means YYYY
注意：在波兰语中，RRRR 表示 YYYY
回答by Brett Walker
Goto to Tools > Preferences. In the tree, select Database > NLS. There are three Date/Time formats available: Date, Timestamp and Timestamp TZ. Editing the Date format gives the desired effect.
转到工具 > 首选项。在树中，选择数据库 > NLS。有三种可用的日期/时间格式：日期、时间戳和时间戳 TZ。编辑日期格式会产生所需的效果。
Like I have said above; this approach has not given me a permanent change.
就像我上面说的；这种方法并没有给我带来永久性的改变。
回答by Ashutosh Chopde
SQL Developer Version 4.1.0.19
SQL 开发人员版本 4.1.0.19
Step 1: Go to Tools -> Preferences
第 1 步：转到工具 -> 首选项
Step 2: Select Database -> NLS
步骤 2：选择数据库 -> NLS
Step 3: Go to Date Format and Enter DD-MON-RR HH24: MI: SS
第 3 步：转到日期格式并输入 DD-MON-RR HH24：MI：SS
Step 4: Click OK.
第四步：点击确定。
回答by Sharan Rajendran
When i copied the date format for timestamp and used that for date, it did not work. But changing the date format to this (DD-MON-YY HH12:MI:SS AM) worked for me.
当我复制时间戳的日期格式并将其用于日期时，它不起作用。但是将日期格式更改为此 (DD-MON-YY HH12:MI:SS AM) 对我有用。
The change has to be made in Tools->Preferences-> search for NLS
必须在工具->首选项->搜索NLS中进行更改
回答by alexR1
I have a related issue which I solved and wanted to let folks know about my solution. Using SQL Developer I exported from one database to csv, then tried to import it into another database. I kept getting an error in my date fields. My date fields were in the Timestamp format:
我有一个相关的问题，我解决了，想让人们知道我的解决方案。使用 SQL Developer 我从一个数据库导出到 csv，然后尝试将其导入另一个数据库。我的日期字段中不断出现错误。我的日期字段采用时间戳格式：
28-JAN-11 03.25.11.000000000 PM
The above solution (changing the NLS preferences) did not work for me when I imported, but I finally got the following to work:
导入时，上述解决方案（更改 NLS 首选项）对我不起作用，但我最终使以下内容起作用：
In the Import Wizard Column Definition screen, I entered "DD-MON-RR HH.MI.SSXFF AM" in the Format box, and it finally imported successfully. Unfortunately I have dozens of date fields and to my knowledge there is no way to systematically apply this format to all date fields so I had to do it manually....sigh. If anyone knows a better way I'd be happy to hear it!
在“导入向导列定义”屏幕中，我DD-MON-RR HH.MI.SSXFF AM在“格式”框中输入“ ”，最终导入成功。不幸的是，我有几十个日期字段，据我所知，没有办法将这种格式系统地应用于所有日期字段，所以我不得不手动完成....叹气。如果有人知道更好的方法，我会很高兴听到它！
