Tricky Date Query
Hello, I need to perform a select that compares two dates I need to return all records that haven't had date_2 set after a given number of days since date_1. I'm sure this can be done in one query but I just can't get my head around this one. Can anyone help? Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Date Query
If I understand you correctly, I think you want rows with date_2 not greater than or equal to date_1 plus some number of days, say 30. Then date_2 needs to be less than date_1 plus 30 days. So, you would use something like: SELECT * FROM yourtable WHERE date_2 date_1 + INTERVAL 30 DAY; The date and time functions are explained in the manual http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html. Michael Lee Denny wrote: Hello, I need to perform a select that compares two dates I need to return all records that haven't had date_2 set after a given number of days since date_1. I'm sure this can be done in one query but I just can't get my head around this one. Can anyone help? Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Date Query
On Tue, 7 Sep 2004, Lee Denny wrote: Hello, I need to perform a select that compares two dates I need to return all records that haven't had date_2 set after a given number of days since date_1. ... WHERE date_2 date_1 + INTERVAL X DAY Where X is the number of days. Assuming this is what you asked, giving more information is always good. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Date Query
Michael, Michael Stassen wrote: Mike Blezien wrote: Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo Yes. How about SELECT IF(DAYOFMONTH(CURDATE()) = 15, CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-15'), CONCAT(YEAR(CURDATE() + INTERVAL 1 MONTH), '-', MONTH(CURDATE() + INTERVAL 1 MONTH),'-15')) AS next15; or better yet SELECT IF(DAYOFMONTH(CURDATE()) = 15, DATE_FORMAT(CURDATE(), '%Y-%m-15'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15; works like a charm, thx's :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tricky Date Query
Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Date Query
Mike Blezien wrote: Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo Yes. How about SELECT IF(DAYOFMONTH(CURDATE()) = 15, CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-15'), CONCAT(YEAR(CURDATE() + INTERVAL 1 MONTH), '-', MONTH(CURDATE() + INTERVAL 1 MONTH),'-15')) AS next15; or better yet SELECT IF(DAYOFMONTH(CURDATE()) = 15, DATE_FORMAT(CURDATE(), '%Y-%m-15'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15; If you have mysql 4.1, there are more date functions, which would allow this alternative: SELECT CURDATE() + INTERVAL MOD(DAY(LAST_DAY(CURDATE())) + 15 - DAY(CURDATE()), DAY(LAST_DAY(CURDATE( DAY AS next15; There may be others. See the manual for all the date and time functions http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
