Oracle to_date，从 mm/dd/yyyy 到 dd-mm-yyyy
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6994680/
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 to_date, from mm/dd/yyyy to dd-mm-yyyy
提问by reforrer
I have all dates inserted into table as varchar2(10) and formatted as 'mm/dd/yyyy'. What I need is the following format 'mm-dd-yyyy' and date data type.
My implementation without PLSQL would be:
我将所有日期作为 varchar2(10) 插入表中，并格式化为“mm/dd/yyyy”。我需要的是以下格式“mm-dd-yyyy”和日期数据类型。我没有 PLSQL 的实现将是：
select day||'-'||month||'-'||year as formatted_date from
(select
extract( day from (select to_date('1/21/2000','mm/dd/yyyy') from dual)) as day,
to_number(extract( month from (select to_date('1/21/2000','mm/dd/yyyy') from dual)),09) as month,
extract( year from (select to_date('1/21/2000','mm/dd/yyyy') from dual)) as year
from dual);
select day||'-'||month||'-'||year as formatted_date from
(select
extract( day from (select to_date('1/21/2000','mm/dd/yyyy') from dual)) as day,
to_number(extract( month from (select to_date('1/21/2000','mm/dd/yyyy') from dual)),09) as month,
extract( year from (select to_date('1/21/2000','mm/dd/yyyy') from dual)) as year
from dual);
Result is: 21-1-2000 not 21-01-2000 as expected.
When adding additional to_date(,) as:
结果是：21-1-2000 不是预期的 21-01-2000。
添加额外的 to_date(,) 时：
to_date(day||'-'||month||'-'||year,'DD-MM-YYYY') as formatted_date
it doesn't even change day and month fields with eachother.
它甚至不会改变彼此的日期和月份字段。
回答by StevieG
You don't need to muck about with extracting parts of the date. Just cast it to a date using to_date and the format in which its stored, then cast that date to a char in the format you want. Like this:
您无需为提取日期的一部分而烦恼。只需使用 to_date 及其存储格式将其转换为日期，然后将该日期转换为所需格式的字符。像这样：
select to_char(to_date('1/10/2011','mm/dd/yyyy'),'mm-dd-yyyy') from dual
回答by álvaro González
I suggest you use TO_CHAR()when converting to string. In order to do that, you need to build a date first.
我建议您TO_CHAR()在转换为字符串时使用。为此，您需要先建立一个日期。
SELECT TO_CHAR(TO_DATE(DAY||'-'||MONTH||'-'||YEAR, 'dd-mm-yyyy'), 'dd-mm-yyyy') AS FORMATTED_DATE
FROM
(SELECT EXTRACT( DAY FROM
(SELECT TO_DATE('1/21/2000', 'mm/dd/yyyy')
FROM DUAL
)) AS DAY, TO_NUMBER(EXTRACT( MONTH FROM
(SELECT TO_DATE('1/21/2000', 'mm/dd/yyyy') FROM DUAL
)), 09) AS MONTH, EXTRACT(YEAR FROM
(SELECT TO_DATE('1/21/2000', 'mm/dd/yyyy') FROM DUAL
)) AS YEAR
FROM DUAL
);
回答by JyoG
select to_char(to_date('1/21/2000','mm/dd/yyyy'),'dd-mm-yyyy') from dual
