了解 TO_CHAR 在 oracle 中转换日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47891506/
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
Understanding TO_CHAR to convert dates in oracle
提问by Hudhaifa Yoosuf
I have an oracle data problem which i would like to understand.
我有一个 oracle 数据问题，我想了解一下。
So below are some queries which i executed and on comments are the outputs.
所以下面是我执行的一些查询，评论是输出。
SELECT SYSDATE FROM DUAL; --19-DEC-17
SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM DUAL; -- 19/12/2017
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL; -- 12/19/2017
My question is what kind of date format does the to_char method expecting as the first parameter ? is there a default date format we need to pass?
我的问题是 to_char 方法期望作为第一个参数的日期格式是什么？是否有我们需要传递的默认日期格式？
So i tried this below query as well which throws an error. if some expert could explain me on how the to_char is working, it would be great.
所以我在下面的查询中也尝试了这个，这会引发错误。如果有专家可以向我解释 to_char 的工作原理，那就太好了。
SELECT TO_CHAR('19/12/2017','DD/MM/YYYY') FROM DUAL;
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
回答by Am_I_Helpful
In your query, what you're using is a string value. Instead, if you want to display using TO_CHAR(), you need to use a datetime type string. In your case, '19/12/2017' is a pure string. Please convert it into date type using TO_DATE() function.
在您的查询中，您使用的是字符串值。相反，如果要使用 TO_CHAR() 显示，则需要使用日期时间类型字符串。在您的情况下， '19/12/2017' 是一个纯字符串。请使用 TO_DATE() 函数将其转换为日期类型。
SELECT TO_CHAR('19/12/2017','DD/MM/YYYY') FROM DUAL; // error
Quoting from the Oracle Docs for TO_CHAR (datetime):
从Oracle Docs 中引用TO_CHAR (datetime)：
TO_CHAR (datetime)converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:
DATE values are converted to values in the default date format.
TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.
TO_CHAR (datetime)将 DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE 或 TIMESTAMP WITH LOCAL TIME ZONE 数据类型的日期时间或间隔值转换为日期格式 fmt 指定格式的 VARCHAR2 数据类型值。如果省略 fmt，则 date 将转换为 VARCHAR2 值，如下所示：
DATE 值转换为默认日期格式的值。
TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE 值将转换为默认时间戳格式的值。
TIMESTAMP WITH TIME ZONE 值将转换为具有时区格式的默认时间戳中的值。
Similarly, the function TO_CHAR() accepts numbers as well.
同样，函数 TO_CHAR() 也接受数字。
Quoting from the Oracle Docs for TO_CHAR (number):
从Oracle Docs 中引用TO_CHAR (number)：
TO_CHAR (number)converts n to a value of VARCHAR2 datatype, using the optional number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.
TO_CHAR (number)使用可选的数字格式 fmt 将 n 转换为 VARCHAR2 数据类型的值。值 n 可以是 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型。如果省略 fmt，则 n 将转换为 VARCHAR2 值，其长度正好足以保存其有效数字。
回答by user13040870
As mentioned above, '19/12/2017' is already a string so you cannot use it with to_char. If you want to do this for testing you would first need to convert '19/12/2017' to a date format using to_date('19/12/2017','DD/MM/YYYY') and then you could use to_char to convert back to a character format. For example, to_char(to_date('19/12/2017','DD/MM/YYYY'),'Mon DD, YYYY') will return 'Dec 19, 2017'.
如上所述，'19/12/2017' 已经是一个字符串，因此您不能将它与 to_char 一起使用。如果您想这样做进行测试，您首先需要使用 to_date('19/12/2017','DD/MM/YYYY') 将 '19/12/2017' 转换为日期格式，然后您可以使用 to_char转换回字符格式。例如， to_char(to_date('19/12/2017','DD/MM/YYYY'),'Mon DD, YYYY') 将返回 'Dec 19, 2017'。
回答by Littlefoot
This
这个
SELECT TO_CHAR('19/12/2017','DD/MM/YYYY') FROM DUAL;
doesn't make sense. '19/12/2017' is a string, so there's no use in converting it to yet another string (as you used TO_CHAR), not to mention format mask.
没有意义。'19/12/2017' 是一个字符串，因此将其转换为另一个字符串是没有用的（就像您使用 TO_CHAR 一样），更不用说格式掩码了。
It would be OK if you used TO_DATE in this statement, which says that you want to convert a string '19/12/2017' into a date value, using 'dd/mm/yyyy' format mask.
如果您在此语句中使用 TO_DATE 就可以了，它表示您想使用 'dd/mm/yyyy' 格式掩码将字符串 '19/12/2017' 转换为日期值。
That's what you did in the first part of your message. SYSDATE is a function which returns DATE data type value, so you applied TO_CHAR to it in order to display it in a format you want. Have a look at Format Models, here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm
这就是您在消息的第一部分所做的。SYSDATE 是一个返回 DATE 数据类型值的函数，因此您对其应用了 TO_CHAR 以便以您想要的格式显示它。看看格式模型，在这里：https: //docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm
