How to get Data from Monday to today's Date

Question

I Have a sample data like this

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')

Query :

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

Here I'm unable to get from Monday to Today's Date means 2016-11-10.I'm Covering all the dates which are coming in this week .

I want to get data like this

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

suggest me the best way

There is another date within this range which is `'2016-11-07'`, do you want it to be displayed? — Viki888, Nov 10 '16 at 11:26
yes it's sample data so we can give the output if it is in scope — mohan111, Nov 10 '16 at 11:30
Possible duplicate http://stackoverflow.com/q/7168874/1048425 — GarethD, Nov 10 '16 at 11:54
@mohan111 So you mean whenever we run the query, you want to get all the current week's (for that particular week) DOB's right? — PowerStar, Nov 10 '16 at 13:02
@balaji i have tried it in other way.any way thanks for suggesting the answer — mohan111, Nov 11 '16 at 08:35

score 1 · Answer 1 · answered Nov 10 '16 at 13:20

DATENAME() will solve your problem.

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

score 0 · Answer 2 · answered Nov 10 '16 at 11:33

0

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

answered Nov 10 '16 at 11:33

Mansoor

4,061
1
17
27

score 0 · Answer 3 · answered Nov 10 '16 at 11:36

Try this

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());

This is giving me proper result.

How to get Data from Monday to today's Date

3 Answers3