Re: Find between char field

On 4/25/2016 10:04 AM, 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?
>

J.O. has a good solution. But if you can change your columns, I would recommend changing this to 201604 or 2016/04. This would make it much easier to compare.

Another alternative would be using a DATE field, i.e. 2016-04-01. That would allow you to use DATE functions (INSERT and UPDATE triggers could easily be implemented to ensure the day is always '01').

Either of these would be more efficient than having to concat and convert every row each time you do this.

A column for year and one for month can work, but it can get awkward if you want to select dates between, i.e. 11/2014 and 02/2016.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================