如何找到当周第一天的日期

How to find the date of the first day of the current week in transact-sql(t-sql)?. What T-SQL statement do we need to use for this?.

如何在transact-sql(t-sql)中找到本星期第一天的日期?我们需要使用什么T-SQL语句?

This is always be a Sunday. For example if we run the sql statement on today(2015-02-18) or any day of the current week it should give the result 2015-02-15 which is a Sunday.

这总是一个星期天。例如，如果我们在今天(2015-02-18)或本周的任何一天运行sql语句，它应该给出2015-02-15的结果，也就是周日。

3 个解决方案

#1

One of possible way to get the date on Sunday for the current week.

这是一种可能的方法，可以在周日得到本周的日期。

SELECT  DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1) AS SundayOfCurrentWeek

#2

Using DATEPART

使用DATEPART

It depends on the settings of the server, but if it is configured to think that weeks start on Sundays, then this would work:

这取决于服务器的设置，但如果它被配置为认为在周日开始的几周，那么这将起作用:

SELECT CAST(DATEADD(day, 1-DATEPART(weekday, GETDATE()), GETDATE()) AS date)

DATEPART with weekday parameter gives the day of the week. Then this number is subtracted from the current date and converted to date to remove the time part.

带工作日参数的日期部分给出一周的日期。然后，这个数字从当前日期减去，并转换为日期以删除时间部分。

When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST.

当datepart是week (wk, ww)或weekday (dw)时，返回值取决于使用set DATEFIRST设置的值。

To see the current setting of SET DATEFIRST, use the @@DATEFIRST function. The setting of SET DATEFIRST is set at execute or run time and not at parse time.

要查看当前设置的DATEFIRST，请使用@@DATEFIRST函数。SET DATEFIRST的设置是在执行或运行时设置的，而不是在解析时设置的。

So to play it safe make sure that DATEFIRST is set to 7 (default, U.S. English), which means Sunday.

为了安全起见，请确保DATEFIRST设置为7(默认，美语)，也就是Sunday。

Using DATEDIFF

使用DATEDIFF

Another variant uses DATEDIFF:

另一种变体使用DATEDIFF:

SELECT DATEADD(wk, DATEDIFF(wk, '20150104', GETDATE()), '20150104')

It would always return Sunday. Magic date 20150104 can be any date that is Sunday.

它总是会在星期天回来。魔法日期20150104可以是任何星期天。

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

指定SET DATEFIRST对DATEDIFF没有影响。DATEDIFF总是将周日作为一周的第一天，以确保函数是确定的。

On the one hand it is good, because it is guaranteed to return Sunday, even if you forget to set DATEFIRST. But, on the other hand it is not flexible and doesn't suit those who consider, say, Monday to be the first day of the week (those that are not in US).

一方面，它是好的，因为它保证会在周日返回，即使你忘记设置日期优先。但是，另一方面，它不灵活，不适合那些认为周一是一周的第一天(那些不在我们身边的人)的人。

#3

use this

使用这个

Select dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) as LastWeekStart
Select dateadd(wk, datediff(wk, 0, getdate()), 0) as ThisWeekStart
Select dateadd(wk, datediff(wk, 0, getdate()) + 1, 0) as NextWeekStart

for more information

的更多信息

GETDATE last month

获取当前日期上个月

#1