Calculate Weekending

[Malc8179]

Hi Guys

i have this in a bit of code


DATEADD(d, - DATEPART(dw, dbo.TimeSheets.Date), DATEADD(d, 2, dbo.TimeSheets.Date))


its returning sundays as the start of a new week not the end of the week (works fine for all other days in the week) any ideas what in doing wrong

 

[simian336]

Use the @@DATEFIRST function to see the current setting of SET DATEFIRST.

See BOL for SET DATEFIRST to change it.

Simi

 

[Jamfool]

Try:

DECLARE @TimeSheets TABLE ([Date] datetime)
INSERT INTO @TimeSheets VALUES ('2010-01-29')
INSERT INTO @TimeSheets VALUES ('2010-01-22')

SELECT
[Date],
DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, [Date])) % 7, [Date]) SundayEOF
FROM @TimeSheets