SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Birthdays in next 7 days


Birthdays in next 7 days

Author
Message
suraj.app
suraj.app
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 27
Hi,
I need to display all the employees whose date of birth comes in the next 7 days. I have tried as below.

declare @start datetime
declare @end datetime

set @start = '1969-01-31' – date is example
set @end = dateadd(dd,26,'1969-01-31') – date is example

select empid,empname,dob
from dob
where datepart(mm,dob) in (datepart(mm,@start),datepart(mm,@end))
and datepart(dd,dob) between (datepart(dd,@start)) and (datepart(mm,@end))

the above query works fine but when it comes to compare date in the where clause it fails as we have dates only till 31.

Please advice.
steveb.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4486 Visits: 7195
try using datediff to get the number of days between @start and the birthday
If it is between 0 and 7incl then their birthday is in the next 7 days


Where DATEDIFF(dd,@start ,dob) > 0 and
DATEDIFF(dd,@start ,dob) <=7


Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28712 Visits: 39977
there may be a more streamlined way to do it, but this works:

--results:
BDayYearBegin dob DayOfBday
1985-01-01 00:00:00.000 1985-09-05 00:00:00.000 247

with cteBirthdays as
(
SELECT convert(datetime,'1962-12-11 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1958-03-28 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-08-30 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-05 00:00:00.000')
)
--use the date add /date diff trick to add the number of DAYS since the beginning of the dob year to get the birthday of this year.
select
--beginning of dob year
DATEADD(yy, DATEDIFF(yy,0,dob), 0) As BDayYearBegin,
dob,
--#days difference
DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,dob), 0) ,dob) As DayOfBday
--# days from
FROM cteBirthdays
WHERE
--#days difference
DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,dob), 0) ,dob)

--midnite of today and 8 days from now
BETWEEN DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) ,getdate())
AND DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) ,getdate()) + 8





Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5884 Visits: 11771
declare @emp table
(empid int identity(1,1) primary key clustered, dob datetime not null)

insert into @emp (dob)
SELECT convert(datetime,'1962-12-11 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1958-03-28 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1960-02-29 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-08-30 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-03 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-04 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-05 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-10 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-11 00:00:00.000')

select
a.*,
BirthdayThisYear = dateadd(yy,datediff(yy,dob,getdate()),dob)
from
@emp a
where
--Birthday between tomorrow and 7 days from today
datediff(dd,getdate(),dateadd(yy,datediff(yy,dob,getdate()),dob))
between 1 and 7



Results:
empid       dob                      BirthdayThisYear
----------- ----------------------- -----------------------
6 1990-09-04 00:00:00.000 2010-09-04 00:00:00.000
7 1985-09-05 00:00:00.000 2010-09-05 00:00:00.000
8 1985-09-10 00:00:00.000 2010-09-10 00:00:00.000

Nevyn
Nevyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 3149
Michael, your code fails when one year rolls over to another

for example, with the test cases and the presumed current date modified:

declare @emp table
(empid int identity(1,1) primary key clustered, dob datetime not null)

insert into @emp (dob)
SELECT convert(datetime,'1962-01-01 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1958-12-31 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1960-02-29 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-08-30 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-03 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-04 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-05 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-10 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-11 00:00:00.000')

select
a.*,
BirthdayThisYear = dateadd(yy,datediff(yy,dob,getdate()),dob),datediff(dd,'Dec 31, 2010' ,dateadd(yy,datediff(yy,dob,getdate()),dob))
from
@emp a

where
--Birthday between tomorrow and 7 days from today
datediff(dd,'Dec 30, 2010' ,dateadd(yy,datediff(yy,dob,getdate()),dob))
between 1 and 7



The expected result is emp 1 and 2 returned, but you only get emp 2. Lowell's has the same problem.
Nevyn
Nevyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1540 Visits: 3149
Here's a fix to Michael's:

declare @emp table
(empid int identity(1,1) primary key clustered, dob datetime not null)

insert into @emp (dob)
SELECT convert(datetime,'1962-01-01 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1958-12-31 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1960-02-29 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-08-30 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-03 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-04 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-05 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-10 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-11 00:00:00.000')

select
a.*,
BirthdayThisYear = CASE
WHEN dateadd(yy,datediff(yy,dob,getdate()),dob) < getdate()
THEN dateadd(yy,datediff(yy,dob,getdate())+1,dob)
ELSE dateadd(yy,datediff(yy,dob,getdate()),dob)
END
from
@emp a

where
--Birthday between tomorrow and 7 days from today
datediff(dd,getdate() ,(CASE
WHEN dateadd(yy,datediff(yy,dob,getdate()),dob) < getdate()
THEN dateadd(yy,datediff(yy,dob,getdate())+1,dob)
ELSE dateadd(yy,datediff(yy,dob,getdate()),dob)
END))
between 1 and 7



Still looks a bit ugly.

All this does is always compare against the NEXT birthday (never a previous one the same year).
WayneS
WayneS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10066 Visits: 10574
This does a compare against birthday of this year, OR next year, being between date range of today thru next 7 days.
I threw in a sample case of someone being born on 2/29 - I found it interesting how when adding years to be the current year, this rolls back to 2/28! I guess this makes since - 3/1 will always be AFTER their birthday, and 7 days prior to that would be either 2/22 (on leap years), or 2/21 (on other years)

DECLARE @dob TABLE (empid int, empname varchar(50), dob datetime);
INSERT into @dob
SELECT 1, 'test1', '19700105' UNION ALL -- this should be selected for dates 12/29-01/05
SELECT 2, 'test2', '19800825' UNION ALL -- this should be selected for dates 8/18 - 8/25
SELECT 3, 'test3', '19820907' UNION ALL -- this should be selected for dates 8/31 - 9/7
SELECT 4, 'test4', '19790910' UNION ALL -- this should be selected for dates 9/3 - 9/10
SELECT 5, 'test5', '19720903' UNION ALL -- this should be selected for dates 8/27 - 9/3
SELECT 6, 'test6', '19850911' UNION ALL -- this should be selected for dates 9/4 - 9/11
SELECT 7, 'test7', '19740102' UNION ALL -- this should be selected for dates 12/26 - 1/2
SELECT 8, 'test8', '19800229' -- born on leap day! should be select for dates 2/22 - 2/29 on leap years, and 2/21 - 2/28 for non-leap years

DECLARE @start datetime,
@end datetime,
@base datetime;

SELECT @base = '20100220', -- test year rollover by changing to '20101231'
@start = DateAdd(day, DateDiff(day, 0, @base), 0),
@end = DateAdd(day, 7, @start);

WITH CTE AS
(
SELECT *,
BirthDay = DateAdd(year, DateDiff(year, dob, @base), dob)
FROM @dob
)
SELECT empid, empname, dob
FROM CTE
WHERE BirthDay between @start and @end
-- to handle when birthday is in beginning of the year,
-- need to add a year to the birthday to check.
OR DateAdd(year, 1, BirthDay) between @start and @end;



Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

vmj
vmj
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 71
Try this, hope this will be easy and simple way
--Try option 1
declare @days as int
set @days = 4
declare @dts as datetime
set @dts = GETDATE()


select name, birth_date
from <table name>
where (
CASE WHEN MONTH(birth_date) < MONTH(@dts) THEN (YEAR(@dts) + 1 ) * 10000
ELSE (YEAR(@dts) + 0 ) * 10000
END
) + MONTH(birth_date)*100 + DAY(birth_date) between convert(int,CONVERT(varchar(8), @dts, 112)) and
convert(int,CONVERT(varchar(8), dateadd(d,@days,@dts), 112))


--Or Try option 2 by creating Function which returns Birthdate as per current running year

-- =============================================
-- Author: VINAY M JADIA
-- Create date: 2012-12-31
-- Description: Function will change the year of given month with replacing date
-- =============================================
ALTER FUNCTION [dbo].[ufnChangeYear]
(
-- Add the parameters for the function here
@changingDate datetime,
@withDate datetime
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @newDate datetime

SELECT @newDate = CONVERT(varchar(4),(
CASE WHEN MONTH(@changingDate) < MONTH(@withDate) THEN
YEAR(@withDate) + 1
ELSE YEAR(@withDate)
END
)
)+ '/' + RIGHT(CONVERT(VARCHAR(8), @changingDate, 11),5)
RETURN @newDate

END



----Query to fetch result using the newly created function
declare @days int
set @days = 365
declare @dtf datetime, @dtt datetime
set @dtf = CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 111))
set @dtt = DATEADD(D, @days, @dtf)

select name, birth_date
from <tablename>
where dbo.ufnChangeYear(birth_date,getdate()) between @dtf and @dtt

--Hope this will work for n no of days even year, month changing


Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 38567
Another way:



declare @CurrentDate datetime;

declare @emp table
(empid int identity(1,1) primary key clustered, dob datetime not null);

insert into @emp (dob)
SELECT convert(datetime,'1962-12-11 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1958-03-28 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1960-02-29 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1960-02-28 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-08-30 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-03 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1990-09-04 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-05 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-10 00:00:00.000') AS dob UNION ALL
SELECT convert(datetime,'1985-09-11 00:00:00.000');

select * from @emp;

set @CurrentDate = '20110222';

select
e.empid,
e.dob,
dateadd(dd, datediff(dd, 0, @CurrentDate), 0) CurrentDate,
dobcheck.UpcomingBirthday
from
@emp e
cross apply (select case when e.dob between dateadd(yy, -(year(@CurrentDate) - year(e.dob)), dateadd(dd, datediff(dd, 0, @CurrentDate), 0)) and
dateadd(dd, 6,dateadd(yy, -(year(@CurrentDate) - year(e.dob)), dateadd(dd, datediff(dd, 0,@CurrentDate), 0)))
then 1
else 0
end) dobcheck(UpcomingBirthday)
where
dobcheck.UpcomingBirthday = 1;

set @CurrentDate = '20110223';

select
e.empid,
e.dob,
dateadd(dd, datediff(dd, 0, @CurrentDate), 0) CurrentDate,
dobcheck.UpcomingBirthday
from
@emp e
cross apply (select case when e.dob between dateadd(yy, -(year(@CurrentDate) - year(e.dob)), dateadd(dd, datediff(dd, 0, @CurrentDate), 0)) and
dateadd(dd, 6,dateadd(yy, -(year(@CurrentDate) - year(e.dob)), dateadd(dd, datediff(dd, 0,@CurrentDate), 0)))
then 1
else 0
end) dobcheck(UpcomingBirthday)
where
dobcheck.UpcomingBirthday = 1;

set @CurrentDate = getdate();

select
e.empid,
e.dob,
dateadd(dd, datediff(dd, 0, @CurrentDate), 0) CurrentDate,
dobcheck.UpcomingBirthday
from
@emp e
cross apply (select case when e.dob between dateadd(yy, -(year(@CurrentDate) - year(e.dob)), dateadd(dd, datediff(dd, 0, @CurrentDate), 0)) and
dateadd(dd, 6,dateadd(yy, -(year(@CurrentDate) - year(e.dob)), dateadd(dd, datediff(dd, 0,@CurrentDate), 0)))
then 1
else 0
end) dobcheck(UpcomingBirthday)
where
dobcheck.UpcomingBirthday = 1;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15119 Visits: 14396
Note: thread is 2+ years old Exclamation

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search