[Resolved] unix_timestamp

jakeone

I've searched the forums and I've seen posts re date comparisons. I'm pretty sure I'm on the right track but can't see what I'm doing wrong:

SELECT vacTitle, vacDateAdded FROM vacancies
WHERE (unix_timestamp(vacDateAdded)

= unix_timestamp(2004-06-12))

The vacDateAdded field is in DATE format (YYYY-MM-DD) as is "2004-06-12".

When I run the query, records are returned which are clearly not greater than 2004-06-12, i.e. 2004-05-09. What am I doing wrong?

Mordecai

Originally posted by jakeone
SELECT vacTitle, vacDateAdded FROM vacancies
WHERE (unix_timestamp(vacDateAdded)

= unix_timestamp(2004-06-12))

Well... 2004-06-12 is 1986 (2004 minus 6 minus 12).

'2004-06-12' is probably what you want.

jakeone

Thanks for the reply.

Unfortunately, it's still not working. I was thinking whether it might have anything to do with how I constructed "2004-06-12",

$currentDate = mktime (0,0,0,date("m"), date("d"), date("Y"));
$dateToday = date("Y-m-d", $currentDate);

I then stored $dateToday in field with DATE format. Grasping at straws? It's all I can think of!

jakeone

OK, fixed it. During testing I noticed phpMyAdmin kept inserting a space before the months and days, i.e. YYYY -MM -DD instead of YYYY-MM-DD - I don't know why - but when I removed the spaces it worked. Cheers.