oracle - Oracle至今，从mm/dd/yyyy到dd-mm-yyyy

我将所有日期都以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);

结果是:21-1-2000而不是预期的21-01-2000。

将其他to_date(，)添加为:

to_date(day||'-'||month||'-'||year,'DD-MM-YYYY') as formatted_date

它甚至不会彼此更改日期和月份字段。

最佳答案

我建议您在转换为字符串时使用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
    );

关于oracle - Oracle至今，从mm/dd/yyyy到dd-mm-yyyy，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6994680/