[Resolved] mySQL date question

[gmp]

i've got a database that inserts records with a time field in the format MM/DD/YYY, instead of a standard timestamp. (bad practice, i know... but unfortunately i didn't write the original code.)

i'm trying to pull the records back out via PHP with a statement like the following, based on a time i'm setting with an earlier form ($criteria_time):

SELECT a, b FROM table WHERE record_time > '$criteria_time'

i've been playing with things like:

$criteria_time = date('n/j/Y', (mktime(0, 0, 0, $month, $day, $year)));

which puts my starting date into a format that matches the one in the database. but i'm having no luck in getting it to work; it just returns every record in the table. can anyone explain how i should be doing this?

i figure i need to tell mySQL to evaluate record_time and criteria_time as dates, but i don't know how to do this. i've tried some permutations of STR_TO_DATE but i keep getting syntax errors. i'm not sure my server's mySQL version is completely up-to-date.

any and all help appreciated.

[jayant]

See SUBSTRING function provided by MySQL and do your job using it.

hint: retrieve year, month, day seperate using it and perform your job.

[Sxooter]

Actually, a most databases parse whatever date you give them and then store them in a non-text (i.e. seconds since christ was born kinda thing) internally, so it shouldn't really matter how they were entered.

This is in postgresql:

create table test (dt timestamp);

insert into test values ('1999-02-03');

insert into test values ('Feb 2, 1999');

select * from test;

dt

1999-02-03 00:00:00
1999-02-02 00:00:00
(2 rows)

In MySQL I get a lot of dates of 0000000000000000 which means it didn't understand 'Feb 2,1999' as a date. Maybe that's what's happening in your database? Can you view the dates as mm/dd/yyy in the database? Are they stored in a proper date / time type?

[gmp]

and... solved! thanks for the tip!

[edit] sorry, sxooter, missed your post before i marked this resolved. the problem, i think, is that these dates are not being stored as proper dates. they're inserted into, and coming out, as mm/dd/yyyy, not in any timestamp format. i wonder if mySQL is treating them as plain strings.

[Sxooter]

What type of field are you storing them in?