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]
