SQL Server returns unexpected week number

Question

I have some orders in a table and the last order date of 2011 is 20th Dec.

I'm using a sql command to calculate the number of orders in a given week:

SELECT CONVERT(VARCHAR(3),DATENAME(week,convert(datetime,order_date,103))) AS week, 
  COUNT(1) as orders 
FROM order_table 
where DATENAME(YEAR,convert(datetime,order_date,103)) = '2011' 
GROUP BY CONVERT(VARCHAR(3),DATENAME(week,convert(datetime,order_date,103))) 
order by week asc

It returns me the some of the following results:

Week | Orders  
41   | 42  
42   | 110  
43   | 115  
...
...  
51   | 155  
52   | 15

The trouble with this is is that the last order date of 2011 as mentioned that I have is 20th Dec 2011, which can't be week 52 so must be week 51.

I've got some other stats(off another system, not SQL server) which giving me some other figures and the last week on it is 51 which I have no doubt is correct. So there's going to be a queries if people are looking both!

Anyone have any idea or know how to sort this?

Thanks,

Answer 1

The iso_week of 20th Dec 2011 is 51. So maybe that is what you need.

SELECT datepart(iso_week, '2011-12-20')

Answer 2

SQL Counts a week as Sunday-Saturday, you can use the script below to see how the weeks break out for 2011. The 1st of January is a Saturday, which means the first week is only 1 day. There are 53 SQL weeks in 2011, and 53 weeks in most years

DECLARE @dStartDate DATETIME

SET @dStartDate = '01/01/2011'

WHILE @dStartDate < '01/01/2012'
BEGIN

PRINT   CONVERT(VARCHAR, @dStartDate, 101) 
        + ' : ' 
        + CONVERT(VARCHAR, DATEPART(WEEK, @dStartDate)) 
        + ' : ' 
        + DATENAME(DW, @dStartDate)

SET @dStartDate = @dStartDate + 1

END

If you want to change the day SQL counts as the first of the week, you can use the DATEFIRST command

http://msdn.microsoft.com/en-us/library/ms181598.aspx