SQL Oracle 日期到字符串的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13591546/
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
Oracle date to string conversion
提问by Ram
I have a string column COL1 when I am doing this
执行此操作时，我有一个字符串列 COL1
SELECT TO_CHAR(TO_DATE(COL1,'dd-mon-yy'), 'mm/dd/yyyy')
FROM TABLE1
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.
COL1 中的数据在 dd-mon-yy 中，例如：27-11-89 和 89 是 1989 但选择将其返回为 11/27/2089。
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)
我必须做一个内部 TO_DATE，因为如果我不这样做，我就会收到无效号码错误（ORA-01722：无效号码）
How can show 1989 instead of 2089? Please help
如何显示 1989 而不是 2089？请帮忙
回答by a_horse_with_no_name
The data in COL1 is in dd-mon-yy
COL1 中的数据在 dd-mon-yy
No it's not. A DATEcolumn does nothave any format. It is only converted (implicitely) to that representation by your SQL client when you display it.
不，这不对。一DATE列并没有任何格式。只有当您显示它时，它才会被 SQL 客户端转换（隐式）为该表示。
If COL1 is really a DATEcolumn using to_date()on it is useless because to_date()converts a string to a DATE.
如果 COL1 确实是一个DATE列，则使用to_date()它是无用的，因为to_date()将字符串转换为 DATE。
You only need to_char(), nothing else:
你只需要 to_char()，没有别的：
SELECT TO_CHAR(col1, 'mm/dd/yyyy')
FROM TABLE1
What happens in your case is that calling to_date()converts the DATEinto 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.
在您的情况下，调用to_date()会将 转换DATE为字符值（应用默认 NLS 格式），然后将其转换回 DATE。由于这种双重隐式转换，一些信息在途中丢失了。
Edit
编辑
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.
最好的（老实说：唯一明智的）解决方案是将该列转换为DATE. 然后，您可以将值转换为您想要的任何表示形式，而无需担心隐式数据类型转换。
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 RRinstead of YY:
但最有可能的答案是“我继承了这个模型，我必须应对它”（它总是如此，显然没有人负责选择错误的数据类型），那么你需要使用RR而不是YY：
SELECT TO_CHAR(TO_DATE(COL1,'dd-mm-rr'), 'mm/dd/yyyy')
FROM TABLE1
should do the trick. Note that I also changed monto mmas your example is 27-11-89which has a number for the month, not an "word" (like NOV)
应该做的伎俩。请注意，我也改变mon来mm作为你的例子是27-11-89具有数月，而不是一个“字”（样NOV）
For more details see the manual: http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00215
有关更多详细信息，请参阅手册：http: //docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00215
回答by Yali
Try this. Oracle has this feature to distinguish the millennium years..
尝试这个。oracle有这个特性来区分千年..
As you mentioned, if your column is a varchar, then the below query will yield you 1989..
正如您所提到的，如果您的列是 varchar，那么下面的查询将产生 1989..
select to_date(column_name,'dd/mm/rr') from table1;
从 table1 中选择 to_date(column_name,'dd/mm/rr');
When the format rr is used in year, the following would be done by oracle.
当格式 rr 用于年份时，以下将由 oracle 完成。
if rr->00 to 49 ---> result will be 2000 - 2049, if rr->50 to 99 ---> result will be 1950 - 1999
如果 rr->00 到 49 ---> 结果将是 2000 - 2049，如果 rr->50 到 99 ---> 结果将是 1950 - 1999
回答by akshay gulawane
Another thing to notice is you are trying to convert a date in mm/dd/yyyy but if you have any plans of comparing this converted date to some other date then make sure to convert it in yyyy-mm-dd format only since to_char literally converts it into a string and with any other format we will get undesired result. For any more explanation follow this: Comparing Dates in Oracle SQL
另一件要注意的事情是您正在尝试以 mm/dd/yyyy 格式转换日期，但如果您有任何计划将此转换后的日期与其他日期进行比较，请确保仅将其转换为 yyyy-mm-dd 格式，因为 to_char 字面意思是将其转换为字符串，如果使用任何其他格式，我们将得到不想要的结果。有关更多解释，请遵循： Comparing Dates in Oracle SQL
回答by Codo
If your column is of type DATE(as you say), then you don't need to convert it into a string first (in fact you would convert it implicitly to a string first, then explicitly to a date and again explicitly to a string):
如果您的列是DATE类型（如您所说），那么您不需要先将其转换为字符串（实际上，您会先将其隐式转换为字符串，然后显式转换为日期，然后再显式转换为字符串)：
SELECT TO_CHAR(COL1, 'mm/dd/yyyy') FROM TABLE1
The date format your seeing for your column is an artifact of the tool your using (TOAD, SQL Developer etc.) and it's language settings.
您在专栏中看到的日期格式是您使用的工具（TOAD、SQL Developer 等）及其语言设置的产物。
