SQL Datepart - Starting the week on Monday instead of Sunday

Question

I have run across an issue in a query using datepart. We have been using the following query to return the last three weeks of data, however we recently found out that corporate is using a reporting week from Monday-Sunday, while the below query is defaulted to Sunday-Saturday. I have tried "SET LANGUAGE BRITISH" and "SET DATEFIRST 1" but I must not have a good grasp on these functions as they do not change my query results. I should mention that we are running on sql server 2000. If you know a solution your help would be appreciated:

declare @name varchar(50);
set @name = 'A name here';

SELECT week, year, CallCount, GoodCalls, CAST(CAST(GoodCalls as float)/CAST(CallCount as float)as decimal (18,4)) as NCP_perc
FROM
(SELECT TOP 3 datepart(ww, a.date_c) as week
,datepart(year, a.date_c) as year
      ,SUM(CallCount_wo_Xfer) as CallCount
      ,ROUND(SUM(CAST((CallCount_wo_Xfer*NCP_wo_Xfer)as float)),0) as GoodCalls
      FROM db1 A
      inner join db2 B
      on a.Agent = b.Name collate database_default
      inner join db3 C
      on b.id = c.id collate database_default
      where c.manager = @name
      group by datepart(year, a.date_c), datepart(ww, a.date_c)) AS T
      order by year desc, week desc

score 3 · Accepted Answer · answered Jul 19 '11 at 13:49

3

Monday is day 2 so try

SET DATEFIRST 2

answered Jul 19 '11 at 13:49

gbn

422,506
82
585
676

score 2 · Answer 2 · answered Sep 11 '14 at 14:47

2

Set DateFirst 2 will skip the first two days, if you want to start from Monday Use Set DateFirst 1

answered Sep 11 '14 at 14:47

Adnan Muhammad

21
1

2

Welcome to Stack Overflow. OP tried using `SET DATEFIRST 1` and reports it changed nothing. Perhaps there's something else wrong, but regardless, this is not helpful until you explain what else OP should try, or why this didn't work for him. – Gutblender Sep 11 '14 at 14:53

SQL Datepart - Starting the week on Monday instead of Sunday

2 Answers2