php and mysql use the date range can not show

Question

I had the question for the mysql date between select. In the mysql table, the field is varchar. The date range is '21-01-2013' and '31-01-2013', it can show the records, but the date range is '21-01-2013' and '20-02-2013', it cannot show the records.

"SELECT * from away_from_office where (awaydatefrom between '21-01-2013' and '31-01-2013') ";

the between operation, cant work on strings, as you mentioned; you need to convert the string to integer value, as shown by the JW user — Ionut Flavius Pogacian
– Ionut Flavius Pogacian, Commented Jan 3, 2013 at 10:07
The solution is to switch to a DATE field not VARCHAR. As answers show you can convert on the fly but that's more of a quickfix/patch than a proper solution. — MrCode
– MrCode, Commented Jan 3, 2013 at 10:07

John Woo · Accepted Answer · 2013-01-03 10:05:50Z

2

you should convert it to date first using STR_TO_DATE, eg.

SELECT * 
from away_from_office 
where awaydatefrom between STR_TO_DATE('21-01-2013', '%d-%m-%Y') and  
                           STR_TO_DATE('31-01-2013', '%d-%m-%Y')

if the column has the same format with the one you've shown, convert it also

WHERE STR_TO_DATE(awaydatefrom, '%d-%m-%Y') BETWEEN

if you have a chance to alter the table, or you're working with sample records, alter your table by changing the data type of the column to DATE.

answered Jan 3, 2013 at 10:05

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Woo Over a year ago

If you feel an answer solved the problem, please mark it as 'accepted' by clicking the white check mark (around 15mins to an hour later). This helps keep the focus on older SO which still don't have answers.

ryder · Accepted Answer · 2013-01-03 10:06:34Z

1

Change column datatype to DATE

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

answered Jan 3, 2013 at 10:06

ryder

515 bronze badges

Collectives™ on Stack Overflow

php and mysql use the date range can not show

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related