September 3, 2010 at 5:45 am
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.
September 3, 2010 at 5:55 am
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
September 3, 2010 at 6:39 am
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
September 3, 2010 at 8:30 am
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
September 3, 2010 at 10:16 am
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.
September 3, 2010 at 10:33 am
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).
September 3, 2010 at 12:20 pm
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
December 31, 2012 at 8:42 pm
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
December 31, 2012 at 9:59 pm
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;
January 1, 2013 at 5:04 am
Note: thread is 2+ years old :exclamation:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 1, 2013 at 5:27 am
opc.three (1/1/2013)
Note: thread is 2+ years old :exclamation:
Was noted, just responding to the alternate way the most recent responder posted. Plus, that is the beauty of this site, it doesn't matter how old a thread is, it still can help someone, and alternatives can still be found to solve the problem.
January 1, 2013 at 5:36 am
Lynn Pettis (1/1/2013)
opc.three (1/1/2013)
Note: thread is 2+ years old :exclamation:Was noted, just responding to the alternate way the most recent responder posted. Plus, that is the beauty of this site, it doesn't matter how old a thread is, it still can help someone, and alternatives can still be found to solve the problem.
Indeed. One of the many great qualities of this site. And in this case how appropriate is it that a thread that requires a solution that must deal with a date range that crosses a calendar-year-boundary would be resurrected on New Years Eve 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 27, 2013 at 10:02 am
Mad props to VINAY M JADIA for the change year function a few replies up. I was having difficulty getting the Day of the Week Name from a birthday in the future. With his ufnChangeYear function, my problem was solved. Granted there are multiple ways to skin this cat, but his approach worked for me. Here's my final compiled code from a few pilfered snippets (Credit and many thanks to the original coders).
Report consumed in a Microsoft Dynamics AX 4.0 SQL2005 Environment so you may need to tweak your Select Statement to make it suitable for your environment.
-- KH 12/27/2013 QUERY FOR SSRS HR BIRTHDAY REPORT
-- *********************************************************************
-- DECLARE AND SET VARIABLES BASED ON DBO.UFNCHANGEYEAR BY VINAY M JADIA.
-- *********************************************************************
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)
-- ESTABLISHES # OF DAYS TO LOOK IN FUTURE FOR EMPLOYEES WITH A BIRTHDAY
-- YOU WILL NEED TO COMMENT OUT "DECLARE @DAYS1" IN SSRS QUERY
DECLARE @DAYS1 DATETIME
-- COMMENT OUT "SET @DAYS1" IN SSRS AND ALLOW USER SPECIFIED INPUT
SET @DAYS1 = 30
-- GET COLUMN NAMES FOR REPORT
SELECT A.EMPLID ,A.FIRSTNAME ,A.LASTNAME ,A.BIRTHDATE ,A.HRMABSENCESETUPID AS [DEPT.] ,A.TITLE
,DBO.UFNCHANGEYEAR(A.BIRTHDATE,GETDATE()) AS [BIRTHDAY_THIS_YEAR]
,CONVERT(VARCHAR(8), A.BIRTHDATE, 1) AS [BIRTH_MM/DD/YY]
,CONVERT(CHAR(12),DATENAME(MONTH, A.BIRTHDATE)) AS [BIRTH_MONTH]
,CONVERT(CHAR(2),DATENAME(DAY, A.BIRTHDATE)) AS [BIRTH_DAY]
,CONVERT(CHAR(4),DATENAME(YEAR, A.BIRTHDATE)) AS [BIRTH_YEAR]
,DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, A.BIRTHDATE, GETDATE())
,A.BIRTHDATE)) + ABS(SIGN(DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, A.BIRTHDATE,
GETDATE()), A.BIRTHDATE))) - 1) / 2 * 365 AS [DAYS_UNTIL_BIRTHDAY]
,DATENAME(DW,(DBO.UFNCHANGEYEAR(A.BIRTHDATE,GETDATE()))) AS [BIRTHDAY_CURRENT_YEAR]
,FLOOR(DATEDIFF(DD,A.BIRTHDATE,GETDATE()) / 365.25) AS [AGE_NOW]
-- SSRS Query will need the following cast as Int statement substituted.
-- ,FLOOR(DATEDIFF(dd,A.BIRTHDATE,GETDATE()+ cast((@DAYS1) as int) ) / 365.25) AS [AGE_ON_BIRTHDAY]
,FLOOR(DATEDIFF(DD,A.BIRTHDATE,GETDATE()+(@DAYS1)) / 365.25) AS [AGE_ON_BIRTHDAY]
FROM EMPLTABLE AS A
WHERE A.STATUS IN (0,1) --PULLS EMPLOYED OR NONE STATUS EMPLOYEES
-- ****************************************************************
-- RESTRICTS DATA FEED TO EMPLOYEES WITH A BIRTHDAY IN NEXT ? DAYS.
-- ****************************************************************
-- SSRS Query will need the following cast as Int statement substitued.
-- AND 1 = (FLOOR(DATEDIFF(dd,A.BIRTHDATE,GETDATE()+ cast((@DAYS1) as int)) / 365.25))
AND 1 = (FLOOR(DATEDIFF(DD,A.BIRTHDATE,GETDATE()+ (@DAYS1)) / 365.25))
-
(FLOOR(DATEDIFF(DD,A.BIRTHDATE,GETDATE()) / 365.25))
ORDER BYDATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, A.BIRTHDATE, GETDATE()), A.BIRTHDATE))
+ ABS(SIGN(DATEDIFF(DAY, GETDATE(),
DATEADD(YEAR, DATEDIFF(YEAR, A.BIRTHDATE, GETDATE()), A.BIRTHDATE))) - 1) / 2 * 365
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply