[sql] In SQL how to compare date values?

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.

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.

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.

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.

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';

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.

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.

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'