strange data comparison issue

speedamp

hi everybody,

a client of mine is story dates in the MYSQL db as '01/06/2013' (MM/DD/YYYY), and in my code i'm getting inconsistent results when doing data comparisions.

01/01/2013 < 01/06/2013 will return TRUE

however 12/01/2012 < 01/06/2013 will return FALSE.

any assistance on this?? would be very easy if i could change their entire DB, but not an option at this point so i need to handle this programatically, hopefully.

thanks,

-Michael

NogDog

What data type is it being stored as? If it's a character/text type, then you'll have to cast it into a date/time type before you can do "date arithmetic" with it, e.g. using str_to_date().

speedamp

yes, it's varchar type.

are there mysql casting options to use within the statement OR do i need to return everyhting and parse the result set as noted?

Bonesnap

As NogDog suggested, double check the data type because it sounds like they're being stored as strings since the date data type in MySQL is YYYY-MM-DD.

speedamp

confirmed. they are stored as Strings in the DB. what are my options in that case?

Derokorian

If you want to do it in mysql you can use the str_to_date() function.

speedamp

PERFECT! thank you

dalecosp

Hi speedamp! Please consider using the "thread tools" above this thread to "mark thread RESOLVED!"

🙂

NogDog

i.e.:

SELECT . . . WHERE STR_TO_DATE(some_date_string_col, '%m/%d/%Y') < STR_TO_DATE(another_date_string_col, '%m/%d/%Y') . . . ;

bradgrafelman

If you've got the option, note that I would say re-designing the DB schema would be an even better route. MySQL has a plethora of Date and Time Functions, but you're tying your own hands when you tell MySQL a column is just a string of characters rather than a DATE (or DATETIME).

johanafm

Use the proper data types to begin with or you will not be using indices when searching. Or, if you really do want to use the wrong data types, at least convert the search clause data, not the column data - i.e. convert the searched dates to strings in the same format as those stored rather than convert the stored data to dates. The first requries one conversion and can then use string indexing (if present), while the latter needs one conversion per row and can't use indexing.

But obviously it would be better to use date or datetime data types with no conversion needed and proper indexing to begin with.

The way this works is exactly the same way it would work if you store date and time as an unsigned integer and use either of the two where clauses

WHERE FROM_UNIXTIME(inttime) > '2012-12-15';

WHERE inttime > UNIX_TIMESTAMP('2012-12-15');

where the first one would NOT use index for inttime since the column needs to be converted, while the second would use index for inttime since it is provided with an unsigned integer to search for.