to_date

SELECT 
    CASE 
        WHEN date_str LIKE '____-__-__ __:__:__' THEN 
            TO_CHAR(CAST(date_str AS TIMESTAMP), 'YYYY-MM-DD')
        WHEN date_str LIKE '____/__/__ __:__:__' THEN 
            TO_CHAR(TO_DATE(date_str, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY-MM-DD')
        WHEN date_str LIKE '________ __:__:__' THEN 
            TO_CHAR(TO_DATE(date_str, 'YYYYMMDD HH24:MI:SS'), 'YYYY-MM-DD')
        WHEN date_str LIKE '____-__-__' THEN 
            TO_CHAR(CAST(date_str AS DATE), 'YYYY-MM-DD')
        WHEN date_str LIKE '____/__/__' THEN 
            TO_CHAR(TO_DATE(date_str, 'YYYY/MM/DD'), 'YYYY-MM-DD')
        WHEN date_str LIKE '________' THEN 
            TO_CHAR(TO_DATE(date_str, 'YYYYMMDD'), 'YYYY-MM-DD')
        ELSE NULL
    END AS formatted_date
FROM your_table;


-- 创建处理多种日期格式的函数
CREATE OR REPLACE FUNCTION format_date_string(date_str TEXT)
RETURNS TEXT AS $$
BEGIN
    -- 尝试不同的日期格式进行解析
    BEGIN
        RETURN TO_CHAR(CAST(date_str AS DATE), 'YYYY-MM-DD');
    EXCEPTION
        WHEN OTHERS THEN
            BEGIN
                RETURN TO_CHAR(TO_DATE(date_str, 'YYYY/MM/DD'), 'YYYY-MM-DD');
            EXCEPTION
                WHEN OTHERS THEN
                    BEGIN
                        RETURN TO_CHAR(TO_DATE(date_str, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY-MM-DD');
                    EXCEPTION
                        WHEN OTHERS THEN
                            BEGIN
                                RETURN TO_CHAR(TO_DATE(date_str, 'YYYYMMDD'), 'YYYY-MM-DD');
                            EXCEPTION
                                WHEN OTHERS THEN
                                    BEGIN
                                        RETURN TO_CHAR(TO_DATE(date_str, 'YYYYMMDD HH24:MI:SS'), 'YYYY-MM-DD');
                                    EXCEPTION
                                        WHEN OTHERS THEN
                                            RETURN NULL; -- 无法解析的格式返回NULL
                                    END;
                            END;
                    END;
            END;
    END;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT format_date_string('2025-12-25') as result;
SELECT format_date_string('2025-12-25 12:00:32') as result;
SELECT format_date_string('2025/12/25') as result;
SELECT format_date_string('2025/12/25 12:00:32') as result;
SELECT format_date_string('20251225') as result;
SELECT format_date_string('20251225 12:00:32') as result;