I’d like to get the number of a week from a DATE starting with Monday as the first day of the week. While WEEK() can partially accomplish this, I would like each week to be uniquely identified. I.e., rather than rolling over at 52 or 53 to 0 or 1, to continue counting to week 54, 55, etc.
What is the best way to accomplish this in SQL?
Advertisement
Answer
If the week numbers should be sequential (perhaps for calculating time spans), you can pick an arbitrary Sunday in the past that should be week 1, count how many days since that day, and divide by 7. (Choosing Sunday will make Monday the start of the week.)
SELECT CEIL( DATEDIFF( '2013-01-04', '1970-01-04' ) / 7 ) AS week; # 2244
If all you need is unique identification, you could use YEARWEEK() to get 201253, 201301 and so on.
SELECT YEARWEEK( '2013-01-04', 1 ) AS week; # 201301