You have a table containing birthdates (date field, including year)
and you want to display all rows for which the birthday will occur in
the next week (seven days).
You tried this query:
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
but found a problem - that sometimes birthdates on or after the 25th
in months with 31 days will not show.
This could happen in December, after December 25, because then the
month field will be 12 but the month field for DATEADD(CURDATE(),
INTERVAL 7 DAY) will be 1 and so nothing will match the clause
"BETWEEN 12 and 1"
I don't immediately see that this would be a problem in other
months. For example:
mysql> select 'fish' from t1 where 3 between 12 and 1;
Empty set (0.00 sec)
mysql> select 'fish' from t1 where 3 between 1 and 12;
+------+
| fish |
+------+
| fish |
+------+
1 row in set (0.03 sec)
One solution is to create a new date from the birthday in the table
by taking the year from the current date and the month and day from
the birthdate and then checking to see if that date is in the next
seven days, that is, between CURDATE() and DATEADD(CURDATE(),
INTERVAL 7 DAY). Here is an example of how that might work:
mysql> show create table birthdays;
+-----------
+-----------------------------------------------------------------------
---------------------------------------------------------------+
| Table | Create
Table
|
+-----------
+-----------------------------------------------------------------------
---------------------------------------------------------------+
| birthdays | CREATE TABLE `birthdays` (
`name` varchar(32) default NULL,
`birthdate` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------
+-----------------------------------------------------------------------
---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from birthdays;
+----------------+------------+
| name | birthdate |
+----------------+------------+
| P.G. Wodehouse | 1881-10-15 |
| John Marquand | 1893-11-10 |
| Ian Flemming | 1908-05-28 |
| John Grisham | 1955-02-08 |
| Jeffrey Archer | 1940-04-15 |
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+----------------+------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM birthdays WHERE STR_TO_DATE(CONCAT_WS('-', YEAR
(CURDATE()), MONTH(birthdate), DAY(birthdate)), '%Y-%m-%d') BETWEEN
CURDATE() AND ADDDATE(CURDATE(), INTERVAL 7 DAY);
+----------------+------------+
| name | birthdate |
+----------------+------------+
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+----------------+------------+
2 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 10:13 PM, Penduga Arus wrote:
On 8/3/06, Penduga Arus <[EMAIL PROTECTED]> wrote:
On 8/1/06, Chris <[EMAIL PROTECTED]> wrote:
> Did you look at the link David sent you?
>
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
thanks.. I manage to do that.. below is my solution. please advice if
there is any better solution
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
I have notice problem with my sql statment above, when it run on the
25th for the month which have 31 days the statment isnt valid anymore.
FYI my a017tkhlahir is in date format (yyyy-mm-dd)
Please advice.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
