将具有不同格式的字符串转换为日期 [英] Convert string with different format to date

问题描述

我有一个带有字符串列( varchar(200))的表，其中包含不同格式的日期.例如

I have a table with a string column (varchar(200)) that contain a date in different format. For example

may 24 1983 12:00AM
1981-01-13 00:00:00
1979-01-13 00:00:00:123

我想将此列转换为日期以提取年份.我该怎么办?我使用了STR_TO_DATE，但效果不佳.我必须为每种不同的格式使用SUBSTRING吗?

I want to convert this column in a date to extract year. How could i do? I have used STR_TO_DATE but don't work well. I must use SUBSTRING for each different format?

MySQL 5.0.95版

MySQL version 5.0.95

推荐答案

在

There's a trick for detecting a valid date on the man page. You can use it to determine whether a STR_TO_DATE format worked.

select foo,
    case when length(date(str_to_date(foo,"%Y-%m-%d %H:%i:%S"))) is not null then str_to_date(foo,"%Y-%m-%d %H:%i:%S")
        when length(date(str_to_date(foo,"%b %d %Y %h:%i%p"))) is not null then str_to_date(foo,"%b %d %Y %h:%i%p")
    end as newdate
from my_table

为您期望的所有人提供一种格式.疯狂测试.

Put one format for everyone you're expecting. Test like crazy.

祝你好运.

(哦，恭喜您尝试清理错误的模式！)

(Oh, and congrats for trying to cleanup a bad schema!)

这篇关于将具有不同格式的字符串转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！