Hi,

I need a help in the query for splitting of the given two dates into weeks.

For instance,

IF the date is between 1-04-2011 to 1-05-2011, it must be split into four weeks- 7/04/2011,14/04/2011,21/04/2011 and 28/04,2011.

Thanks.

for e.g.

if date is 5-4-2011 then you want 7-4-2011
if date is 16-4-2011 then you want 21-4-2011

is it so?

for e.g.

if date is 5-4-2011 then you want 7-4-2011
if date is 16-4-2011 then you want 21-4-2011

is it so?

hi utrivedi,

If date is 5-4-2011 then its +7, which will be 12/04/2011

Thanks.

SELECT DATE_ADD(date_column_name, INTERVAL 7 DAY);
SELECT DATE_ADD(date_column_name, INTERVAL 7 DAY);

hi utrivedi,

Sorry i forgot to mention a condition here.
The condition is between 2 dates.

What i meant was there will be start date and end date given.
Between these 2 dates the week calculation must be done.

For eg.,

Start Date:1/04/2011
End Date:1/05/2011

Between these dates there are four weeks:7/04/2011,14/04/2011,21/04/2011 and 28/04/2011

I want the query for this.

Thanks.

// Use the mysql week() or weekofyear() functions.

No, you cannot retrieve all week numbers between dates in one query without a stored function or procedure. What you would need is a function which returns a result set of more than one row with two input parameters for the limiting dates. I do not think it is possible.
But you could create a table with 366 values (one for each day) and then link it into a join query which selects all the day numbers between two given day numbers. From this result you might build a query which returns the week numbers for the dates in between.

Use the mysql week() or weekofyear() functions.

hi smantscheff,

week() or weekofyear() does not return the dates.
It returns the week number

I want the dates of all the weeks between 2 given dates.

Thanks.