如何通过Toad更改日期格式的默认NLS参数? [英] How do I change the default NLS parameters for date format through Toad?

问题描述

我有一个NLS日期格式为DD-MON-RR.这给了我基础日期格式为YY，而我想将其更改为YYYY.我尝试使用以下查询，并成功运行

I have a NLS date format as DD-MON-RR. This gives me the underlying date format as YY while I want to change it to YYYY. I tried using the following query and it ran successfully

DECLARE
       v_date DATE := sysdate;
BEGIN
       DBMS_OUTPUT.put_line(TO_CHAR(v_date, 'MM/DD/YYYY'));
END;

但这并没有更改默认格式.

But that didn't change the default format.

在某些情况下，我试图将数据从Oracle导入Tableau.不幸的是，当我尝试从Tableau Server导出交叉表时，它查看的是基础数据，而不是视图中的内容.这导致我在Excel中将2017年6月25日更改为17年6月25日.

for some context, I am trying to import data from Oracle to Tableau. Unfortunately when I try to export a crosstab from Tableau server it looks at the underlying data rather than whats on the view. This causes the date that I have as 25-Jun-2017 to change to 25-Jun-17 in the excel.

我唯一能理解的解决方法是更改​​基础数据/源数据的默认格式，在这种情况下为Oracle DB.

The only workaround I have been able to understand is to change the default format of the underlying/source data which in this case is Oracle DB.

我正在使用TOAD，并试图了解如何将其更改为DD/MON/RRRR格式或在year列中用4位数字表示的类似格式.

I am using TOAD and am trying to understand how can I change it to possibly DD/MON/RRRR format or something similar with 4 digits in the year column.

任何解决方法也受到赞赏

Any workaround is also appreciated

推荐答案

使用最合理的方法管理日期格式掩码

首先，我同意Alex关于使用 to_char 的意见.这将是我针对特定要求修改日期掩码的首选.

First of all, I agree with Alex regarding using to_char. This would be my first choice for modifying date masks for specific requirements.

临时在Toad中，您可以根据需要调用alter session命令:

In Toad on an ad hoc basis, you could just invoke the alter session command as needed:

ALTER SESSION SET nls_date_format ='DD/MON/RRRR';

如果您偏爱特定的日期格式掩码(并且您经常看到自己发出命令 ALTER SESSION SET NLS ... )，则可能要考虑更改用户登录设置.

If you are partial to a specific date format mask (and you see yourself often issuing the command, ALTER SESSION SET NLS...) then perhaps you might want to consider changing your user login settings.

如果您仅修改特定的用户首选项登录文件，则为login.sql(请参见

If you just modify your specific user preference login file, login.sql (see here ), your session will adhere to the date format mask of your choosing at the beginning of your session. I am partial to creating the environment variable, SQLPATH, and placing my login script there.

Toad将尊重您的login.sql文件设置(例如，请参见

Toad will honor your login.sql file settings (e.g. see this post).

由于这是由特定要求或个人喜好决定的，因此我永远不会考虑在站点级别将其从默认值修改.

Since this is driven by specific requirements or personal preferences, I would never think of modifying this from default at the site level.

这篇关于如何通过Toad更改日期格式的默认NLS参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！