[RESOLVED] Using MONTH() with varchar...

stevesweetland

Hi guys, recent disaster recovery with server has shown up some flaws in my sql statements.

First off, we're now running MySQL 4.1.19, i cant remember what we were running before.

I USED to be able to do

SELECT * FROM table WHERE MONTH(completion) = '6';

but now, i cant.

The field is a 255 character varchar field (dont ask why) and is in the format

dd/mm/yyyy

Id presume there is somewhere i can change the setup of mysql which would allow this to work, dont really fancy having to write it all out again.

stevesweetland

Incidentally, if i say

SELECT completion, MONTH(completion) from blah WHERE foo = bar

i DO get the 'completion value', but not MONTH(completion), this is returned null.

devinemke

MONTH() is one of mySQL's Date and Time Functions and as such it will only work on mySQL's Date and Time Types. you said not to ask why but i'm afraid i have to: why are you storing your dates as strings and not as dates?

stevesweetland

i wish id had been involved from the beginning, because this would mean that i wouldnt be having this problem now.

as it stands, i have the problem, MONTH(completion) WORKS on 4.0, but not 4.1.

there must be something in a setting that is preventing it...

devinemke

create a new DATE field and write a script that parses thru each row of your VARCHAR field and converts it to a standard mySQL date (YYYY-MM-DD). you can use [man]strtotime[/man] and [man]date[/man] to do this.

stevesweetland

incidentally > this was never fixed.

this is DEFINATELY what the problem is, i tried it on two boxes, one running 4.0 and one 4.1, and its 100% definately a version change.

i had to change how my date functions worked, and stored info in the database.