Mysql按(mmchar-d-yyyy)中的(varchar)日期排序 [英] MySql order by (varchar) date in Mmm-dd-yyyy

问题描述

很遗憾，我的VARCHAR列的日期格式如下:2013年6月13日

So unfortunately I have a VARCHAR column of dates formatted like so: Jun-13-2013

(我知道这做错了，应该以标准格式存储在DATETIME列中)

(I understand that this is done incorrectly and should be stored in a DATETIME column in the standard format)

我试图获取每一行中的所有数据，并按VARCHAR列作为DATETIME进行ORDER，但是我没有找到任何东西可以转换我格式较差的日期列以进行排序.

I'm trying to grab all the data in every row and ORDER by that VARCHAR column as a DATETIME, but I'm not finding anything out there to convert my poorly formatted date column for sorting.

这是我的查询:

$result = mysql_query("SELECT * FROM archive ORDER BY crapdates DESC");

但是我正在尝试将 crapdates 列转换为日期时间...甚至有可能吗?

But I'm trying to convert the crapdates column to a datetime... is this even possible?

我找不到mmm-dd-yyyy格式转换随时随地

感谢签出

推荐答案

您需要

You want the STR_TO_DATE() function to convert your existing varchar column to a date value.

我绝对建议创建一个新列，并使用 STR_TO_DATE()将值从现有列复制到新列中，而不是使用 STR_TO_DATE()直接在 SELECT 的 ORDER BY 中.

I'd definitely recommend creating a new column and using STR_TO_DATE() to copy the value from your existing column into the new column, rather than using STR_TO_DATE() directly in the ORDER BY of your SELECT.

UPDATE archive SET newdate = STR_TO_DATE(crapdate,'%b-%e-%Y');

如果您不能更改表结构，则可以对现有的列进行排序，如下所示:

If you can't change the table structure, you can sort on the existing column like this:

SELECT * FROM archive ORDER BY STR_TO_DATE(crapdate,'%b-%e-%Y') DESC;

这篇关于Mysql按(mmchar-d-yyyy)中的(varchar)日期排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！