sql server datepart return

I have a sql query that is grouping rows by calendar week

select count(*),datepart(wk,mydate)
from MyTable
where mydate between '12/26/2010' and '1/8/2011'
group by datepart(wk,mydate)

The date range is two weeks but three rows come back because Jan 1 is a 开发者_StackOverflow中文版saturday and is the only day in the range that DATEPART returns a 1 the other dates return 53 or 2.

I want jan 1 to be grouped with the dates that return a 53, but I want it to be a generic answer not something like CASE WHEN datepart(wk,mydate) = 53 then 1 else datepart(wk,mydate) end because that will work for that specific date range not for other years.

I'm just wondering what a good solution would be

thanks in advance.

We use to choose as week of a date, the week of his last sunday (first day of the week in SQL). So, for each date, you can ask for the week of his last sunday with the following query:

select count(*),datepart(wk,mydate-DATEPART(dw,mydate)+1)
from MyTable
where mydate between '12/26/2010' and '1/8/2011'
group by datepart(wk,mydate-DATEPART(dw,mydate)+1)

Perhaps you can use iso_week instead of wk.

select count(*),datepart(iso_week,mydate)
from MyTable
where mydate between '12/26/2010' and '1/8/2011'
group by datepart(iso_week,mydate)

Sample:

declare @T table (Val datetime)
insert into @T values
('2010-12-30'),
('2010-12-31'),
('2011-01-01'),
('2011-01-02'),
('2011-01-03'),
('2011-01-04'),
('2011-01-05')

select
  Val,
  datepart(iso_week, Val) as ISO_WEEK
from @T

Result:

Val                     ISO_WEEK
----------------------- -----------
2010-12-30 00:00:00.000 52
2010-12-31 00:00:00.000 52
2011-01-01 00:00:00.000 52
2011-01-02 00:00:00.000 52
2011-01-03 00:00:00.000 1
2011-01-04 00:00:00.000 1
2011-01-05 00:00:00.000 1

Try DateDiff() instead with your start date as the date to compare.