如何将varchar转换/转换为日期? [英] How to convert/cast varchar to date?
本文介绍了如何将varchar转换/转换为日期?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在mySQL 5.1中有一个日期列,其数据类型为varchar(mm-dd-yyyy)
.如何将其转换为DATE?
I have a date column with data type varchar(mm-dd-yyyy)
in mySQL 5.1. How do I convert it to DATE?
这是我到目前为止所拥有的-
Here is what I have so far -
SELECT id, date
FROM tableName
WHERE (CAST((SUBSTRING (date FROM 7 FOR 4 )||'/'||SUBSTRING (date FROM 4 FOR 2 )||'/'||SUBSTRING (date FROM 1 FOR 2 )) AS DATE) >= '01/01/2012' )
ORDER BY date DESC;
获取此
错误-#1064-您的SQL语法有错误;查看手册 对应于您的MySQL服务器版本以获取正确的语法 在"FROM 7 FOR 4"附近使用
error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM 7 FOR 4 )
请帮助.
推荐答案
You can use MySQL's STR_TO_DATE()
function
SELECT id, date
FROM tableName
WHERE STR_TO_DATE(date,'%Y-%m-%d') >= '01/01/2012'
ORDER BY date DESC;
尽管我怀疑您使用Unix时间戳会更轻松
Although I suspect you will have an easier time using Unix Timestamps
SELECT id, date
FROM tableName
WHERE UNIX_TIMESTAMP(STR_TO_DATE(date,'%d/%m/%Y')) >= UNIX_TIMESTAMP('01/01/2012')
ORDER BY date DESC;
这篇关于如何将varchar转换/转换为日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文