Jump to content

Need help with query involving dates


everythingandtwo

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.