ORACLE中的TO_DATE函数 [英] TO_DATE function in ORACLE

问题描述

我正在尝试TO_DATE函数.具体来说，我注意到以下查询

I was trying the TO_DATE function. Specifically, I noted that the following queries

1. SELECT TO_CHAR(TO_DATE('01-01-2015','DD-MM-YYYY'),'DD-MON-YY') FROM DUAL
2. SELECT TO_DATE('01-01-2015','DD-MM-YYYY') FROM DUAL

具有相同的输出:01-JAN-2015.

为什么TO_DATE函数以缩写形式返回月份? 我对第二个查询的预期输出是类似01-01-2015的内容(简单来说是TYPE转换，而不是格式转换).

Why does the TO_DATE function return the month in its abbreviated form? My expected output for the second query is something like 01-01-2015 (simply, a TYPE conversion, NOT a format conversion).

我错了吗?

谢谢

推荐答案

日期没有格式-它们由 7-或8个字节.

Dates do not have a format - they are represented by 7- or 8-bytes.

SELECT DUMP( SYSDATE ) FROM DUAL;

可能输出:

Typ=13 Len=8: 220,7,11,26,16,41,9,0

此格式对于计算机比较日期非常有用，但对人们却没有太大帮助；因此，当SQL客户端(SQL/plus，SQL Developers，TOAD等)显示日期时，它不会显示字节，而是将其显示为字符串.

This format is very useful for computers to compare dates but not so useful to people; so, when the SQL client (SQL/plus, SQL Developers, TOAD, etc) displays a date it does not display the the bytes but displays it as a string.

它是通过隐式调用TO_CHAR()(或其他一些内部日期日期化方法)来实现的，并使用默认格式掩码执行此转换.

It does this by making an implicit call to TO_CHAR() (or some other internal method of stringifying dates) and uses a default format mask to perform this conversion.

SQL/Plus和SQL Developer将使用用户的会话参数NLS_DATE_FORMAT执行此转换-请参见此答案关于这个.

SQL/Plus and SQL Developer will use the user's session parameter NLS_DATE_FORMAT to perform this conversion - see this answer regarding this.

因此，您的第二个查询将隐式地转换为执行某些操作(但几乎可以肯定，效率更高):

So your second query is implicitly being converted to do something approaching this (but, almost certainly, more efficiently):

SELECT TO_CHAR(
         TO_DATE('01-01-2015','DD-MM-YYYY'),
         ( SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
       )
FROM   DUAL

这篇关于ORACLE中的TO_DATE函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！