Jump to content


Need help with query involving dates

  • Please log in to reply
2 replies to this topic

#1 everythingandtwo

  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 27 December 2003 - 02:54 PM

I\'m in the process of creating an employee scheduler for my boss. I have a table containing shift dates; a start date and an end date (i.e. Jan 11th, 2004 - April 10, 2004). I\'m wanting to divide the two dates up into weeks like so...

Jan 11th, 2004 - Jan 17th, 2004
Jan 18th, 2004 - Jan 24th, 2004
Jan 25th, 2004 - Jan 31st, 2004

I want to do this until I get to the last week of the shift which would be April 4th, 2004 - April 10th, 2004. How could I do this with a mysql query? If this can\'t be done in mysql, can it be done with a php function of some sort? I\'ve been looking at the various date functions but nothing seems to jump out at me as being a solution. Can anyone help me out? Thanks.

- Brad

#2 shivabharat

  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 27 December 2003 - 03:30 PM

First thing Please DOnt double post .....

Now to your question

You can use DATE_ADD function which is quiet handy

select DATE_ADD(\'2004-01-11\',INTERVAL 7 DAY)

We know a week has 7 days

The above query will add 7 days to the given date

The result of the above query would be 2004-01-18

Now you have to save the result and add till the date difference is ZERO
SELECT (TO_DAYS(\'2004-04-17\')-TO_DAYS(\'2004-01-11\'))

This query will give the date difference which is 97

So you need to maintain a variable which would also subtract \"7\" from this total and also add to the date.

So when the result is ZERO it means that you have finished.

I hope I am clear
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 everythingandtwo

  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 27 December 2003 - 07:12 PM

simple enough...I\'ll give it a shot.

I double posted because I accidentally posted the first one in the wrong section. If authors had the ability to delete their own posts I would\'ve deleted one of them.

Thanks for the help.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users