Problems with date query

I have a problem.
I programmed a query to check whether a database field containing a year value (typ varchar(4)) is within a given intervals. The script is like that:

$sqlAB=“SELECT …, year FROM …

AND year >= ‘$txYearFrom’
AND year <= ‘$txYearTo’
order by …”;

$txYearFrom … To are a text strings which I read out from input fields like that:

The query result is sometimes wrong. How can I fix that and get reliable results?
Btw the database structure can not be changed…

Thanks all for a good idea…


[email protected]

I suspect that using the <= and >= operator on TEXT (which is basically what you get with a varchar) will tend to give you unexpected results. I think it actually compares hex values of the combined characters and evaluates that.

You should probably store the YEAR in the database as a number (Integer is good), that would make comparison easier and more consistent.

…yes, that would be a good idea, but…the database already exists from another application and I am not allowed to change type…So, please another suggestion?


[email protected]

My first notion is to “CAST” it (see … ggling.php ) but you want to sort it in the database and only pull out what you want. Not pull it out and then sort.

Then as I think about it, there is a cast function in MySql that you could consider. … tions.html

Sponsor our Newsletter | Privacy Policy | Terms of Service