Re: Find between char field

On 04/25/2016 04:04 PM, wilsonfalai_at_gmail.com wrote:
> I have a char field where I keep month and year as follows: 01/2015 or 02/2015 or 12/2015 or 03/2016 ...
> I want to do a search and return all records from a period
> Example:
>
> SELECT ... AND (`periodo_referente` BETWEEN '01/2016' AND '04/2016')
>
> As I have a record for each period (month / year) , should return me 01/2016 , 02/2016 , 03/2016 and 04/2016 . But this search is wrong because returns record where the period is 01/2015 and the other a period unwanted
>
> Could anyone help me solve this select?
>

Convert the string to a datetime

SELECT ... AND SELECT STR_TO_DATE(concat('01/', periodo_referente),'%d/%m/%Y') BETWEEN '01/01/2016' and '01/04/2016'

I would recommend you to redesign your table, having a column for year and another for month, then you don't need to do the conversion and save CPU cycles.

-- 

 //Aho