List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:August 30 2006 3:56am
Subject:Re: select between date
View as plain text  
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
Doug@stripped



On Aug 29, 2006, at 10:13 PM, Penduga Arus wrote:

> On 8/3/06, Penduga Arus <penduga@stripped> wrote:
>> On 8/1/06, Chris <dmagick@stripped> 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/mysql?unsub=1

Thread
select between datePenduga Arus31 Jul
  • RE: select between dateSST - Adelaide)31 Jul
  • RE: select between datePeter Lauri31 Jul
    • Re: select between datePenduga Arus1 Aug
      • Re: select between dateChris1 Aug
        • Re: select between datePenduga Arus3 Aug
          • Re: select between datePenduga Arus30 Aug
            • Re: select between dateDouglas Sims30 Aug