Problem when using DATEADD and DATEDIFF to get the start day (Monday) of the week

I use this sql to get the start day of the week (Monday) when users enter date of other days

@StartDate has format (yyyymmdd)

SQL: CAST(convert(varchar(12),DATEADD(week,DATEDIFF(week,0,@StartDate),0),112) as int

The problem is that when entered date is from Tuesday to Sa开发者_如何学运维turday, it returns to the correct Monday of the current week. Yet when the entered date is sunday, it returns date of monday next week.

can anybody tell me what i did wrong

thank you

Your sunday belongs to same week as monday, this will make monday the first day of the week. It should fix your query

set datefirst 1

This syntax doesn't care what day the database is considering first day of the week, it will calculate the monday of the real week of your @StartDate.

DATEADD(week, DATEDIFF(day, 0, @StartDate)/7, 0)

You can test with this:

SELECT DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0)

This works:

SELECT DATEPART(weekday, DATEADD(day, -1, CONVERT(DATETIME, @StartDate)))

Also, don't forget to set the correct dateformat first:

SET DATEFORMAT ymd

You know how I do this?

SELECT max(cal_date)
FROM calendar
WHERE cal_date <= CURRENT_DATE
  AND day_of_week = 'Mon'

No arithmetic. Fast, runs in .000082 seconds on my old box. The calendar table is indexed; it can be used efficiently in joins on huge tables.

And most important--you can tell the query is obviously right.