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.
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.
Nevermind found an answer. Ty the same for anyone who was willing to reply.
WHERE DATEDIFF(mydata,'2008-11-20') >=0;
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';
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.
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'
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.