Jump to content

Archived

This topic is now archived and is closed to further replies.

everythingandtwo

Need help with query involving dates

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

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.