了解TO_CHAR在Oracle中转换日期 [英] Understanding TO_CHAR to convert dates in oracle

问题描述

我有一个想了解的Oracle数据问题.

I have an oracle data problem which i would like to understand.

因此，下面是我执行的一些查询，并在注释上显示了输出.

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

我的问题是to_char方法期望第一个参数是哪种日期格式?有需要传递的默认日期格式吗?

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.

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.

推荐答案

在您的查询中，您使用的是字符串值.相反，如果要使用TO_CHAR()显示，则需要使用日期时间类型的字符串.在您的情况下，"19/12/2017"是一个纯字符串.请使用TO_DATE()函数将其转换为日期类型.

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.

SELECT TO_CHAR('19/12/2017','DD/MM/YYYY') FROM DUAL;  // error

引用 Oracle Docs for TO_CHAR(日期时间):

TO_CHAR(日期时间)转换日期时间或DATE的间隔值， 时间戳，带时区的时间戳或带本地时区的时间戳 数据类型转换为VARCHAR2数据类型的值，格式为 日期格式fmt.如果省略fmt，则日期将转换为 VARCHAR2值如下:

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值转换为默认日期格式的值.

DATE values are converted to values in the default date format.

TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE的值转换为 值以默认时间戳格式显示.

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()也接受数字.

Similarly, the function TO_CHAR() accepts numbers as well.

从 Oracle文档TO_CHAR(数字):

TO_CHAR(数字)使用以下命令将n转换为VARCHAR2数据类型的值 可选数字格式fmt.值n可以是NUMBER类型， BINARY_FLOAT或BINARY_DOUBLE.如果省略fmt，则n被转换 精确到足以保留其有效值的VARCHAR2值 数字.

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在Oracle中转换日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！