mysql 日期比较有关问题

WBOY
Release: 2016-06-13 10:37:54
Original
799 people have browsed it

mysql 日期比较问题
现在表kucun中有datavalue(varchar,是2012-01-01这样的格式日期)和price(float 11,2)字段

现在我想获取2012-07-10到2012-08-10中的数据我的写法是这样的

1,
SELECT * FROM `kucun` where datavalue between 'UNIX_TIMESTAMP(2012-07-10)' and 'UNIX_TIMESTAMP(2012-08-10)'
结果为空
2,
SELECT * FROM `kucun` WHERE datavalue BETWEEN '2012-07-10' AND '2012-08-10'
结果正确,获取我想要找的数据了

3,
SELECT * FROM `kucun` where UNIX_TIMESTAMP(datavalue)>UNIX_TIMESTAMP(2012-07-10) and UNIX_TIMESTAMP(datavalue)结果为空

为么1和3在为空呢?2这样写正规吗?XXXX-XX-XX 这样的格式日期可以比较吗?

------解决方案--------------------
1、
SELECT * FROM `kucun` where datavalue between 'UNIX_TIMESTAMP(2012-07-10)' and 'UNIX_TIMESTAMP(2012-08-10)'
要删去多余的引号

3、
SELECT * FROM `kucun` where UNIX_TIMESTAMP(datavalue)>UNIX_TIMESTAMP('2012-07-10') and UNIX_TIMESTAMP(datavalue)要补上必须的引号
------解决方案--------------------
UNIX_TIMESTAMP()函数的字符串参数要加引号,这样试试:

SQL code
SELECT * FROM `kucun` where UNIX_TIMESTAMP(datavalue)>UNIX_TIMESTAMP('2012-07-10') and UNIX_TIMESTAMP(datavalue)<unix_timestamp class="clear">
                 
              
              
        
            </unix_timestamp>
Copy after login
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template