views:

796

answers:

5

Using MySQL syntax and having a table with a row like:

mydate DATETIME NULL,

Is there a way to do something like:

... WHERE mydate<='2008-11-25';

I'm trying but not really getting it to work.

+1  A: 

Nevermind found an answer. Ty the same for anyone who was willing to reply.

WHERE DATEDIFF(mydata,'2008-11-20') >=0;
fmsf
You're welcome ;)
Arvo
This looks slow: function call and implicit conversion for every row in the table. There must be a way to compare dates more directly.
Joel Coehoorn
Indexes are unused in this way...
FerranB
+3  A: 

Uh, WHERE mydate<='2008-11-25' is the way to do it. That should work.

Do you get an error message? Are you using an ancient version of MySQL?

Edit: The following works fine for me on MySQL 5.x

create temporary table foo(d datetime);
insert into foo(d) VALUES ('2000-01-01');
insert into foo(d) VALUES ('2001-01-01');
select * from foo where d <= '2000-06-01';
Eli
On my PostgreSQL DBMS, indeed, it works fine. May be a MySQL weakness?
bortzmeyer
Yup it doesn't work in mysql
fmsf
Err, it does too work on MySQL. :) I just tried it to make sure I'm not nuts.What version of MySQL you using fmfs? Do you get an error?
Eli
+2  A: 

In standard SQL syntax, you would use:

WHERE mydate <= DATE '2008-11-20'

That is, the keyword DATE should precede the string. In some DBMS, however, you don't need to be that explicit; the system will convert the DATE column into a string, or the string into a DATE value, automatically. There are nominally some interesting implications if the DATE is converted into a string - if you happen to have dates in the first millennium (0001-01-01 .. 0999-12-31) and the leading zero(es) are omitted by the formatting system.

Jonathan Leffler
Note that, if all the dates are in the ISO 8601 format as shown, string comparisons will work (it is a feature of ISO 8601).
bortzmeyer
If the leading zeroes are included, yes. In theory, if you have dates prior to 1000-01-01, the DBMS might omit leading zeroes. I'm not sure it is very likely, but funnier things have been known. Oracle supports dates BCE (BC); those do not compare reliably as strings.
Jonathan Leffler
A: 

You could add the time component

WHERE mydate<='2008-11-25 23:59:59'

but that might fail on DST switchover dates if mydate is '2008-11-25 24:59:59', so it's probably safest to grab everything before the next date:

WHERE mydate < '2008-11-26 00:00:00'
too much php
A: 

Your problem may be that you are dealing with DATETIME data, not just dates. If a row has a mydate that is '2008-11-25 09:30 AM', then your WHERE mydate<='2008-11-25'; is not going to return that row. '2008-11-25' has an implied time of 00:00 (midnight), so even though the date part is the same, they are not equal, and mydate is larger.

If you use < '2008-11-26' instead of <= '2008-11-25', that would work. The Datediff method works because it compares just the date portion, and ignores the times.

DancingFool