Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Birthdays in next 7 days Expand / Collapse
Author
Message
Posted Friday, September 3, 2010 5:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 29, 2011 7:11 AM
Points: 6, Visits: 23
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.

Post #980138
Posted Friday, September 3, 2010 5:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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

Post #980141
Posted Friday, September 3, 2010 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:30 PM
Points: 12,918, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #980163
Posted Friday, September 3, 2010 8:30 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:39 AM
Points: 3,136, Visits: 11,494
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
Post #980237
Posted Friday, September 3, 2010 10:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
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.
Post #980335
Posted Friday, September 3, 2010 10:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
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).
Post #980347
Posted Friday, September 3, 2010 12:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
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
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
Post #980438
Posted Monday, December 31, 2012 8:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 12:21 PM
Points: 20, Visits: 60
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

Post #1401541
Posted Monday, December 31, 2012 9:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 23,398, Visits: 32,252
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;





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)
Post #1401548
Posted Tuesday, January 1, 2013 5:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 7,127, Visits: 12,656
Note: thread is 2+ years old

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1401576
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse