August 30, 2011 at 1:02 am
Hi,
Assuming that the week starts from Sunday and ends on Saturday,can anyone provide me with a query to find all birthdays falling within the week.
Please try with the following query:
-------------------------------------
declare @People table
(
FullName VARCHAR(30) NOT NULL,
dob DATETIME NULL
)
--select * from @People
declare @CurrDate varchar(25)
set @CurrDate='31 aug 11'
--select Day(@CurrDate),DAY('02 sep 11')
INSERT INTO @People (FullName, dob) VALUES ('Nancy Davolio', '1968-12-08')
INSERT INTO @People (FullName, dob) VALUES ('Andrew Fuller', '1952-09-02')
INSERT INTO @People (FullName, dob) VALUES ('Janet Leverling', '1963-08-20')
INSERT INTO @People (FullName, dob) VALUES ('Margaret Sam', '1958-08-31')
INSERT INTO @People (FullName, dob) VALUES ('Margaret Peacock', '1958-08-25')
INSERT INTO @People (FullName, dob) VALUES ('Steven Buchanan', '1955-08-27')
INSERT INTO @People (FullName, dob) VALUES ('Shine sadasivan', '1985-08-31')
INSERT INTO @People (FullName, dob) VALUES ('Arun TS', '1983-08-30')
The result I need is 'Margaret Sam', '1958-08-31' and 'Andrew Fuller', '1952-09-02'(because the date i pass to the query is '31 aug 11').I know that both fall in different months,but in the same week and this is exactly what i want,birthdays within the same week(sunday t saturday) though the dates may be in 2 different months.
Thanks in advance.
Sunitha
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
August 30, 2011 at 5:09 am
DECLARE @CurrDate datetime;
SET @CurrDate = '20110831';
WITH CurrentWeek
AS
(
SELECT DATEADD(d, DATEDIFF(d, 0, @CurrDate) - DATEPART(weekday, @CurrDate) + 1, 0) AS WeekStart
,DATEADD(d, DATEDIFF(d, 0, @CurrDate) - DATEPART(weekday, @CurrDate) + 8, 0) As WeekEnd
)
,Years100
AS
(
SELECT DATEADD(year, -N.number, C.WeekStart) AS WeekStart
,DATEADD(year, -N.number, C.WeekEnd) AS WeekEnd
FROM CurrentWeek C
-- Use your own nuber table here
CROSS JOIN master.dbo.spt_values N
WHERE [type] = 'P'
AND N.number <= 100
)
SELECT *
FROM @People P
WHERE EXISTS
(
SELECT 1
FROM Years100 Y
WHERE P.dob >= Y.WeekStart
AND p.dob < Y.WeekEnd
)
August 30, 2011 at 5:21 am
Thanks Ken,but this does not give me the expected result.Sorry if i had not made myself clear in the previous post.
What i need is the upcoming birthdays of the current week(ie; if the current date is 30 aug 2011 then the current week is from 28 aug 2011 to 04 sep 2011)which falls on or after the current date.If someone has a birthday on 28th aug and 29th aug that need not be displayed.
I used the following query to find all birthdays of the current week.But i need an additional filter to avoid birthdays before today:-
SELECT FullName,convert(varchar,dob,103) Birthday
from @People
WHERE DATEPART(wk, DATEADD(yy, DATEPART(yy, GETDATE())
- DATEPART(yy, dob), dob)) = DATEPART(wk, GETDATE())
Hope you have understood the scenario..
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
August 30, 2011 at 5:34 am
You should be able to work out what you need to do in the first CTE.
August 30, 2011 at 6:34 am
this seems to work for me;
i'm distilling each date to be the # of days fromt he beginning of the year.
there's alot of dateadd/datediff in there, so it can be a little hard to follow,but it works:
/*
(No column name)FullNamedob
245Andrew Fuller1952-09-02 00:00:00.000
242Margaret Sam1958-08-31 00:00:00.000
242Shine sadasivan1985-08-31 00:00:00.000
241Arun TS1983-08-30 00:00:00.000
*/
declare @People table
(
FullName VARCHAR(30) NOT NULL,
dob DATETIME NULL
)
--select * from @People
declare @CurrDate varchar(25)
set @CurrDate='31 aug 11'
--select Day(@CurrDate),DAY('02 sep 11')
INSERT INTO @People (FullName, dob) VALUES ('Nancy Davolio', '1968-12-08')
INSERT INTO @People (FullName, dob) VALUES ('Andrew Fuller', '1952-09-02')
INSERT INTO @People (FullName, dob) VALUES ('Janet Leverling', '1963-08-20')
INSERT INTO @People (FullName, dob) VALUES ('Margaret Sam', '1958-08-31')
INSERT INTO @People (FullName, dob) VALUES ('Margaret Peacock', '1958-08-25')
INSERT INTO @People (FullName, dob) VALUES ('Steven Buchanan', '1955-08-27')
INSERT INTO @People (FullName, dob) VALUES ('Shine sadasivan', '1985-08-31')
INSERT INTO @People (FullName, dob) VALUES ('Arun TS', '1983-08-30')
select DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,dob), 0),dob),* --number of days since the beginning of the year
from @People
WHERE DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,dob), 0),dob) --number of days since the beginning of the year
between DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),getdate()) --# days since begginning of year
and DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),getdate()) + 7 --# days since begginning of year plus 7 days(or 8?) days
Lowell
August 30, 2011 at 10:17 pm
Thanks Lowell..
I have tried your query as well as the one that follows:
SELECT FullName,convert(varchar,dob,103) Birthday
from @People
WHERE DATEPART(wk, DATEADD(yy, DATEPART(yy, GETDATE())
- DATEPART(yy, dob), dob)) = DATEPART(wk, GETDATE())
and (DATEPART(dy,dob) >= DATEPART(dy,getdate()) OR DATEPART(dy,dob) < DATEPART(dy,getdate())-365 )
In both cases i am getting perfect results except for the date '27 dec 2011'(In that case i expect the result set to be the birthdays on and after 27th december(replace getdate() with '27 dec 2011'),but the result set includes 26th december also(I just dont know why).Pls check by altering the dobs of the insert statements.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
August 31, 2011 at 5:35 am
I finally solved the problem by using the following query:
SELECT FullName,convert(varchar,dob,103) Birthday
from @People
WHERE DATEPART(wk, DATEADD(yy, DATEPART(yy, @CurrDate)
- DATEPART(yy, dob), dob)) = DATEPART(wk,@CurrDate)
and datediff(d,convert(datetime,(cast(datepart(d,dob)as varchar)+'/'+
cast(month(dob)as varchar)+'/'+
cast(year(getdate())as varchar)),103),@CurrDate)<=0
But I am still confused as to why there was problem for '27 dec 2011' for the previous query.
:unsure:
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
August 31, 2011 at 2:15 pm
Birthdays (as opposed to birth dates) are a surprisingly difficult data item to work with because they represent a point on a line (from January 1 through December 31) rather than a fixed date. I've often taken the following (unorthodox) approach when having to get lists of upcoming birthdays (or birthdays falling between two dates).
First, I'll create a non-persistent, computed column (I call it Birthday) on the table containing the date of birth, defined as follows:
ALTER TABLE dbo.CustomerData ADD Birthday AS month(DateOfBirth)*100 + day(DateOfBirth)
This gives you an integer column with values between 101 and 1231 representing a birthday.
Then, when doing the query, I'll do the same with the From/To date defining the calendar interval within which I need to find birthdays.
declare @FromDate datetime
declare @ToDate datetime
set @FromDate = GETDATE() -- this may be passed in to a stored proc, or calculated from today's date or whatever
set @ToDate = DATEADD(DD,7,GETDATE()) -- again, how you determine this is unique to your application
-- now get these as integer values
declare @FromDateINT int
declare @ToDateINT int
set @FromDateINT = MONTH(@FromDate)*100 + DAY(@FromDate)
set @ToDateINT = MONTH(@ToDate)*100) + DAY(@ToDate)
At this point, the query becomes easy...
SELECT FullName, DateOfBirth
FROM CustomerData C
WHERE C.Birthday BETWEEN @FromDateINT and @ToDateINT
... except for ranges that span a year end, such as December 28 through January 3. In that case, you have to smarten up the query a little, like this...
SELECT FullName, DateOfBirth
FROM CustomerData C
WHERE ((@FromDate < @ToDate)
AND (BirthDay between @FromDate and @ToDate))
OR ((@ToDate <= @FromDate)
AND((Birthday >= @FromDate)
OR (Birthday <= @ToDate)))
One advantages of this method is that it handles leap years without a problem.
Curious to know what others think of doing it this way....:ermm:
Rob Schripsema
Propack, Inc.
February 21, 2018 at 3:34 pm
I found this thread, and found it helpful. I have a different solution that may help someone else. Here is my solution. In short, it brings the birthdates of each individual up to the current year, then checks based on current values.
SELECTFullName,
CAST(dob AS DATE) AS dob
FROM@People
WHEREDATEADD(YEAR, YEAR(@CurrDate) - YEAR(dob), dob) BETWEEN @CurrDate AND DATEADD(DAY, 6, @CurrDate)
ORDER BY FullName
The code above checks for birthdays in the next seven days. I know the OP asked for records within the current week, but I'll leave determining the start and end days of the current week as an exercise to others. Any date range can be checked using the BETWEEN clause; determine the start and end date ranges, then plug them in to the statement.
March 1, 2018 at 1:33 pm
fahey.jonathan - Wednesday, February 21, 2018 3:34 PMI found this thread, and found it helpful. I have a different solution that may help someone else. Here is my solution. In short, it brings the birthdates of each individual up to the current year, then checks based on current values.
SELECTFullName,
CAST(dob AS DATE) AS dob
FROM@People
WHEREDATEADD(YEAR, YEAR(@CurrDate) - YEAR(dob), dob) BETWEEN @CurrDate AND DATEADD(DAY, 6, @CurrDate)
ORDER BY FullName
The code above checks for birthdays in the next seven days. I know the OP asked for records within the current week, but I'll leave determining the start and end days of the current week as an exercise to others. Any date range can be checked using the BETWEEN clause; determine the start and end date ranges, then plug them in to the statement.
Here's one that uses the same technique, but uses the supplied @CurrDate as the basis, and pre-computes all the dates needed save the dob coming into the current year:DECLARE @CurrDate AS date = '20110831';
DECLARE @CurrWkDay AS tinyint = DATEPART(weekday, @CurrDate);
DECLARE @CurrYear AS int = YEAR(@CurrDate);
DECLARE @WeekStart AS date = DATEADD(day, 0 - (@CurrWkDay - 1), @CurrDate);
DECLARE @WeekEnd AS date = DATEADD(day, 7 - @CurrWkDay, @CurrDate);
DECLARE @People AS TABLE (
FullName varchar(30) NOT NULL,
dob datetime NULL
);
INSERT INTO @People (FullName, dob)
VALUES ('Nancy Davolio', '1968-12-08'),
('Andrew Fuller', '1952-09-02'),
('Janet Leverling', '1963-08-20'),
('Margaret Sam', '1958-08-31'),
('Margaret Peacock', '1958-08-25'),
('Steven Buchanan', '1955-08-27'),
('Shine sadasivan', '1985-08-31'),
('Arun TS', '1983-08-30');
SELECT @CurrDate AS CurrDate, @CurrYear AS CurrYear,
@CurrWkDay AS CurrWkDay, DATENAME(weekday, @CurrDate) AS CurrWkDayName,
@WeekStart AS WeekStart, DATENAME(weekday, @WeekStart) AS WeekStartDay,
@WeekEnd AS WeekEnd, DATENAME(weekday, @WeekEnd) AS WeekEndDay;
SELECT FullName, CONVERT(date, dob) AS dob, CONVERT(date, DATEADD(year, @CurrYear - YEAR(dob), dob)) AS BirthDay
FROM @People
WHERE DATEADD(year, @CurrYear - YEAR(dob), dob) BETWEEN @WeekStart AND @WeekEnd
ORDER BY MONTH(dob), DATEPART(day, dob), FullName;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply