In SQL how to compare date values

Question

Using MySQL syntax and having a table with a row like   mydate DATETIME NULL    Is there a way to do something like       WHERE mydate lt   2008-11-25     I m trying but not really getting it to work

User · Answer

You could add the time component  WHERE mydate lt   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  lt   2008-11-26 00 00 00

User · Answer

Nevermind found an answer  Ty the same for anyone who was willing to reply   WHERE DATEDIFF mydata  2008-11-20    gt  0

User · Answer

In standard SQL syntax  you would use   WHERE mydate  lt   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

User · Answer

Uh  WHERE mydate lt   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  lt    2000-06-01

User · Answer

Nevermind found an answer  Ty the same for anyone who was willing to reply   WHERE DATEDIFF mydata  2008-11-20    gt  0

User · Answer

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 lt   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  lt   2008-11-26  instead of  lt    2008-11-25   that would work  The Datediff method  works because it compares just the date portion  and ignores the times

User · Answer

You could add the time component  WHERE mydate lt   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  lt   2008-11-26 00 00 00

User · Answer

Nevermind found an answer  Ty the same for anyone who was willing to reply   WHERE DATEDIFF mydata  2008-11-20    gt  0

User · Answer

Uh  WHERE mydate lt   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  lt    2000-06-01

User · Answer

Uh  WHERE mydate lt   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  lt    2000-06-01

User · Answer

In standard SQL syntax  you would use   WHERE mydate  lt   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

User · Answer

In standard SQL syntax  you would use   WHERE mydate  lt   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

User · Answer

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 lt   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  lt   2008-11-26  instead of  lt    2008-11-25   that would work  The Datediff method  works because it compares just the date portion  and ignores the times

User · Answer

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 lt   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  lt   2008-11-26  instead of  lt    2008-11-25   that would work  The Datediff method  works because it compares just the date portion  and ignores the times

User · Answer

Nevermind found an answer  Ty the same for anyone who was willing to reply   WHERE DATEDIFF mydata  2008-11-20    gt  0

User · Answer

You could add the time component  WHERE mydate lt   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  lt   2008-11-26 00 00 00

User · Answer

You could add the time component  WHERE mydate lt   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  lt   2008-11-26 00 00 00

User · Answer

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 lt   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  lt   2008-11-26  instead of  lt    2008-11-25   that would work  The Datediff method  works because it compares just the date portion  and ignores the times

User · Answer

In standard SQL syntax  you would use   WHERE mydate  lt   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

User · Answer

Uh  WHERE mydate lt   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  lt    2000-06-01

[sql] In SQL how to compare date values?

Examples related to sql

Examples related to mysql

Examples related to compare

Examples related to where