Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 18-05-2009
Member
 
Join Date: May 2009
Posts: 60
How to create 7 days query in MySql

Hi,
I had a big of list of my customers in MySQL Database. I want to create a query which can find out the coming birthdays of members within a week. The table of member includes complete information about them. Help to build up a query which can give the information of birthdays within a week. As i will just need to pass query and i can get information about the coming birthdays in a week
Reply With Quote
  #2  
Old 18-05-2009
Member
 
Join Date: Apr 2008
Posts: 2,139
Re: How to create 7 days query in MySql

I am giving you an example of the query which you want to pass. The below information will help you to build your query.
First Create Table.
Code:
CREATE TABLE `birthdays` (
`name` varchar(200) NOT NULL,
`birthday` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Table Data Example
Code:
INSERT INTO `birthdays` VALUES (’BDAY 01′,’2008-12-01′),(’BDAY 02′,’2008-12-02′),
(’BDAY 03′,’2008-12-03′),(’BDAY 04′,’2008-12-04′),(’BDAY 05′,’2008-12-05′),(’BDAY 06′,’2008-12-06′),
(’BDAY 07′,’2008-12-07′),(’BDAY 08′,’2008-12-08′),(’BDAY 09′,’2008-12-09′),(’BDAY 10′,’2008-12-10′),
(’BDAY 11′,’2008-12-11′),(’BDAY 12′,’2008-12-12′),(’BDAY 13′,’2008-12-13′),(’BDAY 14′,’2008-12-14′),
(’BDAY 15′,’2008-12-15′),(’BDAY 16′,’2008-12-16′),(’BDAY 17′,’2008-12-17′),(’BDAY 18′,’2008-12-18′),
(’BDAY 19′,’2008-12-19′),(’BDAY 20′,’2008-12-20′),(’BDAY 21′,’2008-12-21′),(’BDAY 22′,’2008-12-22′),
(’BDAY 23′,’2008-12-23′),(’BDAY 24′,’2008-12-24′),(’BDAY 25′,’2008-12-25′),(’BDAY 26′,’2008-12-26′),
(’BDAY 27′,’2008-12-27′),(’BDAY 28′,’2008-12-28′),(’BDAY 29′,’2008-12-29′),(’BDAY 30′,’2008-12-30′),
The Query
Code:
select name, concat(date_format(birthday, ‘%m’),’-', date_format(birthday, ‘%d’)) 
from birthdays where ((date_format(birthday, ‘%m%d’) between date_format(curdate(), ‘%m%d’) 
and if((month(curdate()) = 12 and day(curdate()) > 24), date_format(str_to_date(’12312008′, ‘%m%d%y’),
‘%m%d’), date_format(adddate(curdate(), interval 7 day), ‘%m%d’)))
Reply With Quote
  #3  
Old 18-05-2009
Member
 
Join Date: Jan 2008
Posts: 3,751
Re: How to create 7 days query in MySql

Below is an example of table. You will need to create the table in the below type and pass the query on the base of it.
Select from your table
WHERE
Code:
         birth_date LIKE CONCAT( CURRENT_DATE(), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 2 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 3 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 4 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 6 DAY), DATE_FORMAT('%d/%m'), '%')
         OR birth_date LIKE CONCAT( DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY), DATE_FORMAT('%d/%m'), '%')
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to create 7 days query in MySql"
Thread Thread Starter Forum Replies Last Post
Calculate Working days in MySql DeMario Software Development 4 06-11-2009 06:00 PM
MySQL query in a query Rail racer Software Development 3 21-07-2009 07:06 PM
Turn on MySQL query cache to speed up query performance DMA2Superman Software Development 3 07-07-2009 10:26 AM
MySQL IF ELSE in Query Ryder Software Development 3 27-03-2009 05:24 PM
Days Since Last Login IN ADUC Query sivaranjan Active Directory 4 10-03-2008 04:55 PM


All times are GMT +5.5. The time now is 02:19 PM.