CURDATE and DATE not formatted the same?

[tonynoriega]

My column "entry_time" is set to data format of "DATE" which i can see is returning in this format: yyyy-mm-dd hh:mm:ss

what i am trying to do is see how many records were entered yesterday, not a total of the past two days, but just yesterday...

i know there are 34 records, but am getting "0" returned as the count....

i dont know if CURDATE is not calculating correctly against my "entry_time" date becuase of the hh:mm:ss...is that the case?

SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) = entry_time AS yesterday

[SteveLetwin]

i dont know if CURDATE is not calculating correctly against my "entry_time" date becuase of the hh:mm:ss...is that the case?

I'd say yes. Are you sure the type of entry_time is DATE and not DATETIME?

Do a 'describe registration_table;' or 'show create table registration_table;' to make sure.

[dev.mysql.com ] gives some useful info including the following:

Prior to MySQL 5.0.42, when DATE values are compared with DATETIME values the time portion of the DATETIME value is ignored. Starting from MySQL 5.0.42, a DATE value is coerced to the DATETIME type by adding the time portion as '00:00:00'.

So if entry_time is of type DATETIME, then you should get it working by replacing entry_time in the where clause with cast(entry_time as date).

[tonynoriega]

you were absolutely freaking right....it is DATETIME....

i have two other fields set to DATE, but "entry_time" is a DATETIME data format...

so my question now, is how can i calculate DATETIME against CURDATE and get the proper results?

SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) = entry_time AS yesterday

[SteveLetwin]

you were absolutely freaking right....it is DATETIME....

Well, as the enterprising pedant, I strive to be right all the time. :)

so my question now, is how can i calculate DATETIME against CURDATE and get the proper results?

To repeat myself: replace entry_time in the where clause with cast(entry_time as date).

SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) = cast(entry_time as date)

And I'm not 100% certain, but I'm pretty sure ending a select statement with "AS alias" is invalid.

[tonynoriega]

didnt read that last line......i was too pissed off that i misread the datat format of that field...

in any case...

again....you were absolutely freakin right...

CAST(entry_time AS DATE) works like a charm...

and i didnt end the query with that, because it looks like this:'
<snippet>
SUM(CASE WHEN DATE_SUB(CURDATE(),INTERVAL 6 DAY) = CAST(entry_time AS DATE) THEN 1 END) AS sevendaysback
</snippet>

dude, i freaking love this site forum way better than some other mysql forum that gives me nothing other an snide remarks...

U da man...

[SteveLetwin]

No snide remarks! Oh noes!

I wonder if that's because I'm a 'New User' rather than a 'Senior Member'.

[edited by: SteveLetwin at 5:17 pm (utc) on Aug. 23, 2007]

[tonynoriega]

i dont know.... but when i ask questions on other forums, i get the infamous...

"you should read the manual"

well no crap...i did, and i still cant figure out why this wont work, and hence me asking in the forum....

some people live to be a$$holes on the forums....

in any case, thanks...

learned something new.