18

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.

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
fmsf
  • 36,317
  • 49
  • 147
  • 195

5 Answers5

27

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
  • 5,500
  • 1
  • 29
  • 27
  • On my PostgreSQL DBMS, indeed, it works fine. May be a MySQL weakness? – bortzmeyer Nov 26 '08 at 08:13
  • 2
    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 Nov 26 '08 at 16:17
13

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
  • 730,956
  • 141
  • 904
  • 1,278
  • 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 Nov 26 '08 at 08:12
  • 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 Nov 26 '08 at 17:56
12

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

WHERE DATEDIFF(mydata,'2008-11-20') >=0;
fmsf
  • 36,317
  • 49
  • 147
  • 195
  • 4
    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 Nov 25 '08 at 20:10
3

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
  • 88,666
  • 34
  • 128
  • 138
0

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
  • 1,247
  • 1
  • 8
  • 10