Birthdays in next 7 days

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Birthdays in next 7 days

12 posts, Page 1 of 2

1 2 »»

Birthdays in next 7 days

Rate Topic

Display Mode

Topic Options

Author

Message

suraj.app

Posted Friday, September 03, 2010 5:45 AM

Forum 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

steveb.

Posted Friday, September 03, 2010 5:55 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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

Lowell

Posted Friday, September 03, 2010 6:39 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

Michael Valentine Jones

Posted Friday, September 03, 2010 8:30 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 2,944, Visits: 10,508

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

Nevyn

Posted Friday, September 03, 2010 10:16 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 6:14 PM
Points: 267, Visits: 1,102

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

Nevyn

Posted Friday, September 03, 2010 10:33 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 6:14 PM
Points: 267, Visits: 1,102

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

WayneS

Posted Friday, September 03, 2010 12:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:35 PM
Points: 6,369, Visits: 8,232

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

vmj

Posted Monday, December 31, 2012 8:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 9:12 PM
Points: 1, Visits: 33

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

Lynn Pettis

Posted Monday, December 31, 2012 9:59 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 21,615, Visits: 27,445

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

opc.three

Posted Tuesday, January 01, 2013 5:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 6,720, Visits: 11,756

Note: thread is 2+ years old Exclamation

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401576

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions