当使用DATEADD和DATEDIFF获取本周的开始日(星期一)时出现问题

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

我使用这个sql获取用户输入其他日期的星期(星期一)的开始日

@StartDate has format (yyyymmdd)

@StartDate格式(名称)

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

SQL:铸造(转换(varchar(12),返回(一周,DATEDIFF(一周,0,@StartDate),0),112)为int

The problem is that when entered date is from Tuesday to Saturday, 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

有人能告诉我我做错了什么吗?

3 个解决方案

#1

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.

这个语法不关心数据库考虑的是一周的第一天是星期几，它将计算出@StartDate的真实星期的星期一。

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

You can test with this:

你可以这样测试:

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

#2

This works:

如此:

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

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

另外，不要忘记先设置正确的日期格式:

SET DATEFORMAT ymd

#3

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.

没有算术。快，在我的旧盒子上运行。000082秒。将日历表编入索引;它可以有效地用于大型表的连接。

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

最重要的是，你可以看出这个查询显然是正确的。

#1