TEXT(MONTH(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR(TODAY() - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ ' to ' +
TEXT(MONTH((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(DAY((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))
+ '/' +
TEXT(YEAR((TODAY() + 7) - MOD(TODAY() - DATE(1900, 1, 7), 7)))
which on today's date (02/14/2013), would return:
2/10/2013 to 2/17/2013
I utilized the formula provided in this post to get the current offset on the day of the week. Using that, I just subtracted from today. Unfortunately, you wanted the date formatted outside of the base format of a date which would be 2013-02-14. To take account for that, I had to do some manipulation and just pull in the month, day, and year of the date.
This has more to do with the ISO 8601 Date format than Salesforce. A week in a year starts on the first thursday in the calendar.
ISO 8601 Week Dates
If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year (there is no week 00). 28 December is always in the last week of its year.
Edit:
While the above is true, it doesn't apply to you. I see what you are saying now. It seems to work when formatting this way:
DateTime.newInstanceGmt(Date.newInstance(2014, 1, 5).toStartOfWeek(), Time.NewInstance(12,0,0,0)).format('w')
Best Answer
There are Date Literals you can use in the query criteria. I am not sure if you are looking for last calendar week or last 7 days week, but you are covered either way plus several others-
[SELECT Id, Name FROM Opportunity WHERE CloseDate = LAST_WEEK]or
[SELECT Id, Name FROM Opportunity WHERE CloseDate = LAST_N_DAYS:7]