Query

[Arthi Raman]

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.

[urtrivedi]

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?

[Arthi Raman]

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.

[urtrivedi]

SELECT DATE_ADD(date_column_name, INTERVAL 7 DAY);

[Arthi Raman]

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.

[smantscheff]

// 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.

[Arthi Raman]

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.