MySQL: Going from week of the year to date of the Monday

of that week.

[HughMungus]

So I know how to get the week of the year from a date.

What I want to know is if there is a way in MySQL to get the date of the Monday of a week specified by a week number.

For example, I have a bunch of items in a database with dates. I know how to tell the database to get only the items that occur in, say, week 5 of the year, but how do I get the date of the Monday of that week?

I found a way to do it in PHP but for performance reasons I wanted to know if it's possible inside the MySQL statement.

Also, as an aside, is it possible to assign a value to a "variable" in a MySQL statement (e.g., select something AS somename) then use that as an argument later in the statement?

TIA

[coopster]

SELECT DATE(DATE_SUB('2004-07-30', INTERVAL DAYOFWEEK('2004-07-30')-2 DAY));

You need to subtract 2 in the interval because the "DAYOFWEEK" array starts with 1.

Sunday = 1 
Monday = 2 
etc.

<edit>
Referred to wrong array "DAYNAME" as opposed to "DAYOFWEEK" in explanation
</edit>

[edited by: coopster at 2:28 pm (utc) on July 30, 2004]

[RonPK]

SELECT * FROM table WHERE WEEK(datefield) = 5 AND WEEKDAY(datefield) = 0;

Or, if your weeks start on Sunday and and your counting starts at 1 ;) :

SELECT * FROM table WHERE WEEK(datefield) = 5 AND DAYOFWEEK(datefield) = 2;

More info in the manual, section date and time functions [dev.mysql.com].

[coopster]

>>Also, as an aside, is it possible to assign a value to a "variable" in a MySQL statement (e.g., select something AS somename) then use that as an argument later in the statement?

Missed this part. Yes, you can. This is called an ALIAS [dev.mysql.com]. Standard SQL doesn't allow you to use an

ALIAS

in a

WHERE

clause, though. Details in link provided.

SELECT 
 datefield, 
 DATE(DATE_SUB(datefield, INTERVAL DAYOFWEEK(datefield)-2 DAY)) AS monday, 
 FROM mytable 
 WHERE ... 
;

[HughMungus]

SELECT DATE(DATE_SUB('2004-07-30', INTERVAL DAYOFWEEK('2004-07-30')-2 DAY));

But how do I get any date from that week based on the week number? I'll try the other example...

[HughMungus]

SELECT * FROM table WHERE WEEK(datefield) = 5 AND WEEKDAY(datefield) = 0;

Thanks for the answer but maybe I wasn't clear.

I already have the week number. From that I need to figure out how to get the date of the Monday of that week. The example above seems to be how to get the Monday from a date value, not a week number value (?)

I looked in the manual under date/time functions and haven't yet found where it shows how to do that (which is one reason why I was asking about being able to use an alias in the same SQL statement in which it was generated).

A little more clarification: What I'm trying to do is create an archive of articles based on the week in which each article appears, e.g., "Week of Monday, July 26, 2004", "Week of Monday, July 19, 2004" etc.

[RonPK]

Aha, I get it now. Building on Coopster's query:

SELECT id, title, author, datefield,  
 DATE(DATE_SUB(datefield, INTERVAL WEEKDAY(datefield) DAY)) AS monday,  
 FROM articles  
 WHERE WEEK(datefield) = $week AND YEAR(datefield) = $year

This will return all articles in the given week, with a field with that week's Monday. The value of monday will be the same in every row.

If there are no articles in the given week, the query will return an empty set. So maybe it's better to use the PHP method?

[HughMungus]

Thanks Ron. But I'm still getting an error.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(DATE_SUB(datefield, INTERVAL WEEKDAY(datefield) DAY)) AS monda

I don't expect you guys to do this for me, just wanted to know if it's possible.

I might just do it the PHP way...

[coopster]

Probably because of the DATE function which isn't available until MySQL 4.1.1. You don't need it so just get rid of it. I don't even remember why I had it in there in the first place ;)

SELECT id, title, author, datefield, 
 DATE_SUB(datefield, INTERVAL WEEKDAY(datefield) DAY) AS monday, 
 FROM articles 
 WHERE WEEK(datefield) = $week AND YEAR(datefield) = $year 
;

[HughMungus]

OK, that last one worked. I got it working with PHP also but this will perform better. Thanks again coop!