在sql中按当前日期查找星期的日期范围？

I want to find record by this week just declare current date. Example:

我想在本周找到记录,只是宣布当前日期。例:

     select datename(dw,getdate())  -- Example today is Friday "27-04-2012"

so how can i get date range

那么我怎样才能获得日期范围

     start on monday "23-04-2012" or sunday "22-04-2012"  As @dateStart to

     end on sunday "29-04-2012"  or saturday "28-04-2012" As @dateEnd

then i can select query by

然后我可以选择查询

     select * from table where date>=@dateStart  AND date<=@dateEnd

3 个解决方案

#1

Here are some helpful commands to get each day of the week

以下是一些有用的命令,可以获得一周中的每一天

SELECT 
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2) SatOfPreviousWeek,
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) SunOfCurrentWeek,
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek,
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 1) TuesOfCurrentWeek,
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) WedOfCurrentWeek,
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 3) ThursOfCurrentWeek,
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 4) FriOfCurrentWeek,
    DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) SatOfCurrentWeek

you would then use these in your query to get the date range:

然后,您将在查询中使用这些来获取日期范围:

SELECT *
FROM yourTable
WHERE yourDate >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) -- Sunday
AND yourDate <= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) -- Saturday

#2

So you want only records from this week?

所以你只想要本周的记录?

SELECT t.* 
FROM table t 
WHERE t.date >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
AND   t.date <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0)

#3

The queries that others have suggested may work, but you haven't defined what a "week" is for you. Depending on the country/region and the business reasons, a week could be Sun-Sat, Mon-Sun, Mon-Fri etc.

其他人建议的查询可能有效,但您还没有为您定义“周”。根据国家/地区和商业原因,一周可能是周日,周一至周日,周一至周五等。

Therefore the best solution for finding the first and last days of the week is probably a calendar table where you pre-populate the first and last days of the week for every date that you'll need. Then you can use a simple query to get the correct start and end dates to use in your query:

因此,找到一周的第一天和最后一天的最佳解决方案可能是一个日历表,您可以在其中预先填写您需要的每个日期的一周的最后一天和最后几天。然后,您可以使用简单查询来获取要在查询中使用的正确开始日期和结束日期:

select @StartDate = FirstDayOfWeek, @EndDate = LastDayOfWeek
from dbo.Calendar
where BaseDate = @SomeDate

This solution also has the advantage that you can easily work with different definitions of a week by adding new columns to your calendar table.

此解决方案还具有以下优点:您可以通过向日历表添加新列来轻松使用一周的不同定义。

#1