Oracle 日期到字符串的转换 [英] Oracle date to string conversion

问题描述

我在执行此操作时有一个字符串列 COL1

I have a string column COL1 when I am doing this

SELECT TO_CHAR(TO_DATE(COL1,'dd-mon-yy'), 'mm/dd/yyyy')
FROM TABLE1

COL1 中的数据在 dd-mon-yy 中，例如:27-11-89 和 89 是 1989 但选择将其返回为 11/27/2089.

The data in COL1 is in dd-mon-yy, eg: 27-11-89 and 89 is 1989 but the select returns it as 11/27/2089.

我必须做一个内部 TO_DATE，因为如果我不这样做，我就会收到无效号码错误(ORA-01722:无效号码)

I have to do an inner TO_DATE because if I don't then I am getting an invalid number error (ORA-01722: invalid number)

如何显示 1989 而不是 2089?请帮忙

How can show 1989 instead of 2089? Please help

推荐答案

COL1 中的数据在 dd-mon-yy

不，不是.DATE 列没有有任何格式.仅当您显示它时，它才会(隐式地)由您的 SQL 客户端转换为该表示.

No it's not. A DATE column does not have any format. It is only converted (implicitely) to that representation by your SQL client when you display it.

如果 COL1 确实是一个 DATE 列，在它上面使用 to_date() 是没有用的，因为 to_date() 会将字符串转换为 DATE.

If COL1 is really a DATE column using to_date() on it is useless because to_date() converts a string to a DATE.

你只需要 to_char()，没有别的:

You only need to_char(), nothing else:

SELECT TO_CHAR(col1, 'mm/dd/yyyy') 
FROM TABLE1

在您的情况下，调用 to_date() 将 DATE 转换为字符值(应用默认 NLS 格式)，然后将其转换回 DATE.由于这种双重隐式转换，一些信息在途中丢失了.

What happens in your case is that calling to_date() converts the DATE into a character value (applying the default NLS format) and then converting that back to a DATE. Due to this double implicit conversion some information is lost on the way.

编辑

因此，您在将日期存储在字符列中确实犯了一个大错误.这就是您现在遇到问题的原因.

So you did make that big mistake to store a DATE in a character column. And that's why you get the problems now.

最好的(老实说:唯一明智的)解决方案是将该列转换为 DATE.然后，您可以将值转换为您想要的任何表示形式，而无需担心隐式数据类型转换.

The best (and to be honest: only sensible) solution is to convert that column to a DATE. Then you can convert the values to any rerpresentation that you want without worrying about implicit data type conversion.

但最有可能的答案是我继承了这个模型，我必须应对它"(它总是如此，显然没有人负责选择错误的数据类型)，那么你需要使用 RR 而不是 YY:

But most probably the answer is "I inherited this model, I have to cope with it" (it always is, apparently no one ever is responsible for choosing the wrong datatype), then you need to use RR instead of YY:

SELECT TO_CHAR(TO_DATE(COL1,'dd-mm-rr'), 'mm/dd/yyyy')
FROM TABLE1

应该可以解决问题.请注意，我还将 mon 更改为 mm 作为您的示例是 27-11-89 ，它具有月份的数字，而不是单词"(比如11 月)

should do the trick. Note that I also changed mon to mm as your example is 27-11-89 which has a number for the month, not an "word" (like NOV)

有关详细信息，请参阅手册:http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00215

For more details see the manual: http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00215

这篇关于Oracle 日期到字符串的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！