Selecting date range using only year and week number

[Novocaine88]

Hi, i have a small problem with a query im trying to run.

 

I have a table that has 'Year', 'Week' and some other irrelevant fields.

The year number ranges from 2009 up until the current year

The week number goes from 1 to 53  - I know 52 weeks in a year, but ive been told some years have 53 weeks, and subsequently my data has this, but again this is not necessarily important.

 

So say I want to retrieve all the rows from week 40 in 2009 up until week 44 2011. My brain is having a hard time working this out, im sure its a lot simpler than im making it out to be in my head, but i just cant seem to get there.

 

This is my current attempt at getting the result, even before i tested it i was pretty sure it would fail  :-\

$StartYear = 2009;
$StartWeek = 40;
$EndYear = 2011;
$EndWeek= 44;
$ID= "a1";
$SQLCommand = sprintf("SELECT * FROM WeekYear WHERE ( (Year >= '%d' AND Week  >= '%d') AND (Year <= '%d' AND Week <='%d') ) AND ID = '%s' ORDER BY Year, Week", $StartYear, $StartWeek, $EndYear, $EndWeek, $ID);

 

This obviously doesn't work because the query is only looking for week numbers between 40 - 44, rather than week 40 - 52/3 in 2009, week 1-52/3 in 2010 and week 1-44 in 2011.

Can anyone help me out here, i've been staring at this for too long and nothing is coming to me.

 

Cheers

[PFMaBiSmAd]

Make a 'composite' of the year and week (you will need a leading zero on the week so that the format will ALWAYS be the same length) -

 

WHERE CONCAT(year,week) BETWEEN $StartYear$StartWeek AND $EndYear$EndWeek

 

Edit: You can use the mysql LPAD() function to give a leading zero on the week -

 

WHERE CONCAT(year,LPAD(week,2,'0')) BETWEEN $StartYear$StartWeek AND $EndYear$EndWeek

 

The $StartWeek and $EndWeek also must have a leading zero to always give two digits, which you can do directly in the sprintf() statement.

 

[fenway]

Why not use MySQL's WEEK() function?

[Novocaine88]

Make a 'composite' of the year and week (you will need a leading zero on the week so that the format will ALWAYS be the same length) -

 

WHERE CONCAT(year,week) BETWEEN $StartYear$StartWeek AND $EndYear$EndWeek

 

Edit: You can use the mysql LPAD() function to give a leading zero on the week -

 

WHERE CONCAT(year,LPAD(week,2,'0')) BETWEEN $StartYear$StartWeek AND $EndYear$EndWeek

 

The $StartWeek and $EndWeek also must have a leading zero to always give two digits, which you can do directly in the sprintf() statement.

 

Thanks, I've not come accross the CONCAT function before, but this did the trick nicely.

 

Why not use MySQL's WEEK() function?

Again this is not a function i was aware of. Correct me if im wrong but a quick google and it seems that the data needed for that function (one field: YYYY-MM-DD) would not be in same format as the data i have in my database (two fields( Year: YYYY, Week: WW))

[fenway]

You can use STR_TO_DATE() and %V and %X.