Quoted:
Ok, this is really ticking me off. I just did this not a month ago and can't remember how I got it to work. I'm trying to search my database and find every row with a date later than x. Here's what I've got:

select
en.first_name, en.last_name, en.suffix, en.street_address, en.city, en.state, en.zip_code, en.po_box
from
pubacc_en en, pubacc_hd hd
where
en.call_sign = hd.call_sign and
DATEDIFF('01-01-2009', hd.grant_date) > 1

It took me a while the last time and I thought I saved the query but I thought wrong. When I run this I get no records back even though I know I have records later than the 1st of the year. Where'd I go wrong?

Quoted:



I don't use DATEDIFF a lot, but you can try specifying the first field fully.  i.e. '01-01-2009 00:00:00'.  That may do the trick.

I usually do everything in epoch time, so I would use "where FROM_UNIXTIME('01-01-2009') > FROM_UNIXTIME(hd_grant_date)"

Hope that helps.

Quoted:
Man this is driving me up the wall. I know it wasn't this painful the last time.

My problem is that the column is defined as a char(10). Unfortunately if I change it to a date type it clears every date in that column.

I've tried calling STR_TO_DATE to convert the char to a date type that I can then use with DATEDIFF. That didn't work either.

For example, if I query:
select grant_date from pubacc_hd where call_sign='JaxShooter' it returns 09/08/2006.

If I query:
select STR_TO_DATE(grant_date, '%Y-%m-%d') from pubacc_hd where call_sign='JaxShooter' it returns an empty resultset.

I've tried damned near every permutation, conversion, function that I can think of and can't figure it out. Damn this is pissing me off. You're probably right about trying too hard, though. I know I'm going to kick myself when it finally works.