I'm trying to extract data from a MySQL server with PHP. The information has a date field like this YYYYMM (fx. 201108). My question is how would an SQL query look if I wanted to return the first or last half of a specific year? I have tired something like this, however the lessthan seems to be ignored:
SELECT * FROM `poster` WHERE mdr开发者_StackOverflow LIKE '2011%' AND mdr < '%06'
I'm afraid google havnt been much help for me.
you can do this, but not with VARCHAR or TEXT:
SELECT * FROM `poster` WHERE mdr < '201106' AND mdr > '201100'
You can only use the %
and _
wildcards with the LIKE
statement.
This will work, but using any function on a field will kill any opportunity to use indexes, slowing things down.
SELECT * FROM poster WHERE mdr LIKE '2011%' AND right(mdr,2) > '06'
See:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Try th folowing:
SELECT * FROM `poster` WHERE mdr LIKE '2011%' AND MONTH(STR_TO_DATE(mdr, '%Y%m')) < 06
TRY ( assuming that datatype is varchar
)
SELECT SUBSTRING('YYYYDDMM',FROM 0 FOR 2) AS firstHalf,
SUBSTRING('YYYYDDMM',FROM 2 FOR 2) AS SecondHalf
FROM yourTable
References
string functions
datetime functions
精彩评论