在不合格格式的日期之间转换（更改/斜杠/到-dash-） [英] Converting between illogically formatted dates (changing /slash/ to -dash- )

问题描述

我正在重建一个具有许多不一致性的旧应用程序。我必须将所有数据从旧数据库迁移到新的结构。

I am rebuilding a web application from an old one with many inconsistencies. I have to migrate all the data over from the old database to our new structure.

在旧数据库中，日期存储在MySQL数据库中为 VARCHAR 。 我们位于英国，所以日期是以DD / MM / YYYY 格式编写的。我需要将这些日期转换为MySQL的本机 DATE（）格式。

In the old database, dates were stored in the MySQL DB as VARCHAR. We are based in the UK, so dates were written in the format DD/MM/YYYY. I need to convert these dates to MySQL's native DATE() format.

问题是这个 - PHP默认为假设日期是美式格式（MM / DD / YYYY），因为它们最初是以 / 而不是 - - 和 - 强制PHP假定他们是欧洲格式。

Problem is this - PHP defaults to assuming the dates are in 'American' format (MM/DD/YYYY) because they were originally split with / rather than - - and - forces PHP to assume they are 'European' format.

目前为止，我正在做这些转换：

I am doing this so far to convert them:

$start_date = date('Y-m-d', strtotime($query->row('startdate')));

其中 $ query-> row（'startdate'）是存储日期的旧数据库中的列。问题是，我需要首先将所有的$ code> 21/03/1994 s切换到$ code> 21-03-1994 。

Where $query->row('startdate') is the column in the old database which was storing the dates. Problem is, I need to first switch all the 21/03/1994s to 21-03-1994.

我该怎么做？

推荐答案

$start_date = date('Y-m-d', strtotime(str_replace('/', '-', $query->row('startdate'))));

还是更好 - 只是更改数据库中的数据：

Or better yet - just change the data in the database:

UPDATE `table` SET `startdate` = REPLACE(`startdate`, '/', '-');

...然后将字段转换为DATE类型。

... and then convert the field to type DATE.

----编辑----

---- EDIT ----

其实，沙勒尔上校有一点...我忽略了一个事实，即日期需要反转，所以它是YYYY-MM-DD;假设原始日期格式为DD / MM / YYYY更好的查询可能是这样的：

Actually, Col. Shrapnel has a point ... I'd overlooked the fact that the date needs reversing as well so it's YYYY-MM-DD; assuming the original date is in the format DD/MM/YYYY a better query might be something like:

UPDATE `table` SET `date` = CONCAT(SUBSTRING(`date`, 7), '-', SUBSTRING(`date`, 4, 2), '-', SUBSTRING(`date`, 1, 2))

哪些将组件部分转换成可以转换为DATE的字符串...如果原始日期字符串不使用前导零 1/6/2011 例如...在这种情况下需要做一些更聪明的事情。

Which will reverse the component parts into a string that can be converted to a DATE ... it won't quite work if the original date string doesn't use leading zeroes 1/6/2011 for instance... would need to do something a little cleverer in that case.

这篇关于在不合格格式的日期之间转换（更改/斜杠/到-dash-）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！