-1

I want my first day of the week to be as Sunday but my issue with my code is that it starts always as Monday.

SELECT 
    UID,
    DATEADD(DD,CONVERT(INT, (DATEDIFF(DD, '1/1/1900', t.DT)/7)) * 7,'1/1/1900') [WeekBeginDate], 
    SUM(HOURS) AS TOTAL_HOURS 
FROM 
    myTable t 
WHERE 
    DT >= DATEADD(WEEK, -6, GetDate()) 
GROUP BY 
    UID, CONVERT(INT, DATEDIFF(DD, '1/1/1900', t.DT)/7)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
moe
  • 5,149
  • 38
  • 130
  • 197
  • Try this. https://msdn.microsoft.com/en-us/library/ms181598.aspx – Sean Lange Feb 08 '16 at 14:49
  • 1
    Possible duplicate of [How to set monday as first day of week in SQL Server](http://stackoverflow.com/questions/20031802/how-to-set-monday-as-first-day-of-week-in-sql-server) – Tab Alleman Feb 08 '16 at 14:53
  • it is not a duplicate, i have tried to solve it but having some issues with my solution. thanks – moe Feb 08 '16 at 15:00
  • What did you try and what issues did you have? The knowledge contained in the answer to the duplicate should be sufficient to solve your problem. – Tab Alleman Feb 08 '16 at 15:03
  • I fail to see how this would even get close to being a duplicate of the linked question. The linked question is regarding set datefirst which has nothing to do with the logic calculating sunday as the first date(Calculating being the key word) – t-clausen.dk Feb 08 '16 at 15:16

1 Answers1

1

This should do the trick:

SELECT
  UID,  
  DATEADD(d, -1, DATEDIFF(d, -1, t.DT)/7 * 7) [WeekBeginDate] ,
  SUM(HOURS) AS TOTAL_HOURS 
FROM myTable t 
WHERE DT >= DATEADD(WEEK, -6, GetDate()) 
GROUP BY UID, DATEDIFF(d, -1, t.DT)/7
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92