在Oracle中将日期格式'yyyy/mm/dd'更改为'mm-dd-yyyy' [英] change date format 'yyyy/mm/dd' to 'mm-dd-yyyy' in Oracle

问题描述

我已插入Oracle中的表中.我没有PLSQL的实现将是:

I have inserted into a table in Oracle. My implementation without PLSQL would be:

SELECT to_date('1900-01-01','YYYY-MM-DD') + (rownum - 1) AS DT_CAL, 
       rownum AS NUM_JOUR 
FROM   dual 
CONNECT BY to_date('1900-01-01','YYYY-MM-DD') + (rownum - 1) <= 
                                                      to_date('2000-12-31','YYYY-MM-DD')

结果是:05/28/1900，而不是1900-05-28.您能帮助我了解问题出在哪里吗?

result is: 05/28/1900, not 1900-05-28. Can you help me understand what the problem is?

推荐答案

DATE数据类型没有格式. Oracle将其存储为 7字节或8字节，并且直到将其传递到客户端程序(即SQL)之前/Plus，SQL Developer，Toad，Java，Python等)，然后该客户端程序会根据日期具有格式的任何规则对其进行格式化.

The DATE data type does not have a format; Oracle stores it as either 7- or 8-bytes and it is not until it is passed to a client program (i.e. SQL/Plus, SQL Developer, Toad, Java, Python, etc) and that client program formats it according to whatever rules it has that the date gets a format.

如果使用的是SQL/Plus或SQL Developer，则它将使用NLS_DATE_FORMAT会话参数来格式化日期. 您可以使用以下方式更改:

If you are using SQL/Plus or SQL Developer then it will use the NLS_DATE_FORMAT session parameter to format the date. You can change this using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

(请注意，这只会更改当前会话中的格式，而不会更改任何其他会话/用户的格式.)

如果要给日期指定特定格式，则需要

If you want to give the date a particular format then you will need to convert it to a string.

这篇关于在Oracle中将日期格式'yyyy/mm/dd'更改为'mm-dd-yyyy'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！