CODEWITHSUNDEEP
sqlsql-server-2000datepart
user852062
user852062

Reputation: 55

SQL Datepart - Starting the week on Monday instead of Sunday

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

Upvotes: 3

Views: 4939

Answers (2)

Adnan Muhammad
Adnan Muhammad

Reputation: 21

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

Upvotes: 2

gbn
gbn

Reputation: 432271

Monday is day 2 so try

SET DATEFIRST 2

Upvotes: 3

Related Questions