如何获取从周一到今天的数据

Question

我有一个这样的样本数据

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','2016-11-13')
INSERT INTO @T (ID,Name,DOB) VALUES (6,'Manasa','2016-11-10')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','2016-11-07')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'Manny','2016-10-30')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'kamal','2016-11-01')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Raj','2016-11-08')

查询：

SELECT * from @T WHERE 
DOB >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) 
AND DOB <= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) 

在这里，我无法从周一到今天的日期意味着2016-11-10。我涵盖了本周即将到来的所有日期。

我想得到这样的数据

ID  Name    DOB
6   Manasa  2016-11-10
5   Raj     2016-11-08

建议我最好的方法

Answer 1

DATENAME()将解决您的问题。

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','2016-11-13')
INSERT INTO @T (ID,Name,DOB) VALUES (6,'Manasa','2016-11-10')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','2016-11-07')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'Manny','2016-10-30')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'kamal','2016-11-01')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Raj','2016-11-08')

SELECT * from @T 
WHERE DATENAME(WEEK,DOB) = DATENAME(WEEK,GETDATE())
GO

Answer 2

DECLARE @StartDate DATE = DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0),@EndDate DATE = GETDATE() SELECT * FROM @T WHERE DATEDIFF(DAY,DOB,@StartDate) <= 0 AND DATEDIFF(DAY,DOB,@EndDate) >= 0 

Answer 3

尝尝这个

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','2016-11-13')
INSERT INTO @T (ID,Name,DOB) VALUES (6,'Manasa','2016-11-10')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','2016-11-07')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'Manny','2016-10-30')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'kamal','2016-11-01')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Raj','2016-11-08')


SELECT * from @T WHERE 
DOB >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
AND DOB <= DATEADD(wk, 0,GETDATE());

这给了我适当的结果。