Re: select between date
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 (-mm-dd) Please advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select between date
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 (-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]
Re: select between date
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select between date
Hi, Try here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Penduga Arus [mailto:[EMAIL PROTECTED] Sent: Monday, 31 July 2006 9:03 PM To: mysql@lists.mysql.com Subject: select between date I want to do a program to display birthday for our staff. I have a field named birthday with date format (-mm-dd), from this field I want to display the staff who will have their birthday start from current date to 7 days a head. please help, thanks in advance -- 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]
RE: select between date
What version of MySQL do you have? Depending on that, there are different methods. -Original Message- From: Penduga Arus [mailto:[EMAIL PROTECTED] Sent: Monday, July 31, 2006 6:33 PM To: mysql@lists.mysql.com Subject: select between date I want to do a program to display birthday for our staff. I have a field named birthday with date format (-mm-dd), from this field I want to display the staff who will have their birthday start from current date to 7 days a head. please help, thanks in advance -- 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]
Re: select between date
Penduga Arus wrote: On 7/31/06, Peter Lauri [EMAIL PROTECTED] wrote: What version of MySQL do you have? Depending on that, there are different methods. MySQL 5, can you please show me how to do it.. Did you look at the link David sent you? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html If you can't work it out, post the SQL you are trying to use and the results you expect to get and someone might be able to help you further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
