我对sql查询有问题。现在我有 GETDATE(( 例如今天是星期三,我需要在周一晚上之间约会。和 GETDATE(( 将在这两个日期
今天的例子是星期四 18.05.2017 我想要日期在 15.05.2017和 22.05.2017 之间
我找不到任何解决方案。我怎样才能在查询中的 where 语句中编写它。
SELECT * FROM MATCHES
WHERE ...
提前致谢
要接收第一个星期一: SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)
第二个: SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()) + 1, 0)
http://joelabrahamsson.com/getting-the-first-day-in-a-week-with-t-sql/
也
在 SQL Server 中获取一周的第一天
这是解决方案首先创建一个日历表,然后
declare @startDate datetime = dateadd(week, datediff(week, 0, getdate()), 0);
declare @endDate datetime = DATEADD(DAYS,7,@startDate)
SELECT Date
FROM dbo.Calendar
WHERE Date >= @startDate
AND Date < @endDate ;
如果不存在,请创建日历表
IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name =
'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Calendar]
END
CREATE TABLE [Calendar]
(
[CalendarDate] DATETIME
)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO [Calendar]
(
CalendarDate
)
SELECT
@StartDate
SET @StartDate = DATEADD(dd, 1, @StartDate)
END
然后,使用以下查询获取输出
declare @start datetime = dateadd(week, datediff(week, 0, getdate()), 0);
declare @end datetime = DATEADD(DAY,8,@start)
SELECT [CalendarDate]
FROM Calendar
WHERE CalendarDate BETWEEN @start AND @end