在 Oracle 中将 mm/dd/yyyy 转换为 yyyy-mm-dd
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18103128/
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
Convert mm/dd/yyyy to yyyy-mm-dd in Oracle
提问by MontyPython
The date column that I have is in varchar2and I want to convert those values in YYYY-MM-DD
我拥有的日期列在varchar2，我想将这些值转换为YYYY-MM-DD
DATE
7/26/2013
7/29/2013
8/1/2013
8/4/2013
7/28/2013
7/31/2013
8/3/2013
7/30/2013
8/5/2013
7/25/2013
8/2/2013
8/6/2013
7/27/2013
回答by a_horse_with_no_name
You should neverstore dates in a VARCHARcolumn
你永远不应该在VARCHAR列中存储日期
So in order to display it differently now, you need to first convert the string to a date and then back to a string
所以现在为了以不同的方式显示它，您需要先将字符串转换为日期，然后再转换回字符串
If you are certain that all "dates" do have the right (and same) format, then the following should work:
如果您确定所有“日期”都具有正确（且相同）的格式，那么以下内容应该有效：
select to_char(to_date(date, 'mm/dd/yyyy'), 'yyyy-mm-dd')
from the_table;
But I wouldn't be surprised if that gives you an error because one or more rows have a date which is formatted differently. Something which could not have happened had you defined the column to be of type DATEright from the beginning.
但是，如果这会给您带来错误，我不会感到惊讶，因为一行或多行的日期格式不同。如果您DATE从一开始就将列定义为类型，则不可能发生的事情。
You should really, really consider changing that column to be a real DATEcolumn.
您真的应该考虑将该列更改为真正的DATE列。
Btw: DATEis a horrible name for such a column. It is a reserved word and it simply doesn't tell you anything about what is actually stored in that column (a "start date", an "end date", a "birth date", a "due date", ...)
顺便说一句：DATE对于这样的专栏来说，这是一个可怕的名字。它是一个保留字，它根本没有告诉您有关该列中实际存储的内容的任何信息（“开始日期”、“结束日期”、“出生日期”、“截止日期”…… )
回答by Robert
You have to convert your string to dateand than convert to charwith expected format
您必须将字符串date转换为char预期格式，然后再转换为
select TO_CHAR(TO_DATE(datecol, 'MM/DD/YYYY'),'YYYY-MM-DD')
from your_table
回答by Luv
回答by Alexander Kudrevatykh
you can use REGEXP_REPLACE function to manipulate strings or to_date and to_char to convert string to date and than to string back with different format.
您可以使用 REGEXP_REPLACE 函数来操作字符串或 to_date 和 to_char 将字符串转换为日期，而不是将字符串转换为不同的格式。
