在varchar中按日期排序 [英] order by date in varchar
问题描述
我有varchar格式的日期(2014年1月16日)..
我必须按照日期格式的数据订购...
当我按日期使用简单的订单时...按工作顺序只在月份和日期而不是年份..eg
10/12/2013
31/11/2013
01/01/2014
我的查询提供了这个结果..如何用年基数管理这些数据..比如
01/01/2014
2013年10月12日
31/11/2013
转换varchar
s到date
s(例如,参见 Stack overflow上的mySQL将varchar转换为日期 [ ^ ])在订购之前。
有几种方法可以实现您的目标
1.转换你的字符串到目前为止[时间]
SELECT `date_column`
FROM `your_table`
ORDER BY str_to_date(` date_column`,' %d /%m /%Y') DESC
2.将日期字符串重新格式化为yyyy-mm-dd(或yyyy / mm / dd等)
SELECT `date_column`
FROM `your_table`
...
ORDER BY CONCAT_WS(' - ',SUBSTRING(`date_column`, 7 , 4 ),SUBSTRING(`date_column`, 4 , 2 ),SUBSTRING(`date_ column`, 1 , 2 )) DESC
3.分别按年,月和日订购
SELECT `date_column`
FROM `your_table`
...
ORDER BY CAST(SUBSTRING(`date_column`, 7 , 4 ) AS INT ),CAST(SUBSTRING(`date_column`, 4 , 2 ) AS INT ),CAST(SUBSTRING(`date_column`, 1 ,< span class =code-digit> 2 )) AS INT ) DESC 跨度>
i have date in varchar format(16/01/2014)..
and i have to order by my data in date format...
when i am using simple order by date..its order by works only in month and date not with year..eg
10/12/2013
31/11/2013
01/01/2014
my query provide this result..how to manage this data with year bases also..like
01/01/2014
10/12/2013
31/11/2013
Convert thevarchar
s todate
s (see, for instance "mySQL convert varchar to date" at Stack Overflow[^]) before ordering.
There are several way to achieve your goal
1. Convert your string to date[time]
SELECT `date_column` FROM `your_table` ORDER BY str_to_date(`date_column`, '%d/%m/%Y') DESC
2. Reformat your date string to yyyy-mm-dd (or yyyy/mm/dd etc)
SELECT `date_column` FROM `your_table` ... ORDER BY CONCAT_WS('-', SUBSTRING(`date_column`, 7, 4), SUBSTRING(`date_column`, 4, 2), SUBSTRING(`date_column`, 1, 2)) DESC
3. Order by years, months and days separately
SELECT `date_column` FROM `your_table` ... ORDER BY CAST(SUBSTRING(`date_column`, 7, 4) AS INT), CAST(SUBSTRING(`date_column`, 4, 2) AS INT), CAST(SUBSTRING(`date_column`, 1, 2)) AS INT) DESC
这篇关于在varchar中按日期排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!