I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||oups !
that needs a little reajustment
on moment please...|||Thanks for the quick reply! i'll give it a bash!
Originally posted by Karolyn
SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||now it returns everything, because the birthdays are in the 1970s & 80s compared to the current year which is 2004.
Originally posted by jacmat
Thanks for the quick reply! i'll give it a bash!|||it doesn't work
I'm creating a function for you
that you'll able to use the get the good results|||just a few minutes ...|||select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30|||create this function
CREATE function dbo.DatePart(@.Date Varchar(26), @.Format VarChar(20))
returns Varchar(10) as
begin
return( (case @.Format
when 'YYYY' then Convert(char(4), Year(@.Date))
when 'MM' then Replicate('0', Len(Cast(Month(@.Date) as char(2)))) + Cast(Month(@.Date) as char(2))
when 'DD' then Replicate('0', Len(Cast(Day(@.Date) as char(2)))) + Cast(Day(@.Date) as char(2))
end))
end|||SELECT * FROM Employes
Where
DateDiff(day,
Cast(
RTrim(dbo.DatePart(getdate(), 'YYYY')) + '-' +
RTrim(dbo.DatePart('2002-01-02','DD')) + '-' +
RTrim(dbo.DatePart('2002-01-02','MM')) as datetime),getdate())<=30|||r937 solution's is quite is simpler...|||I always forget the convert format number (like 101)
very practical...
I'll remember this|||Karolyn, you have to put getdate() as the 2nd parameter in DATEDIFF because you want birthdays 30 days after today, not 30 days before|||a soooooo little detail
or put -30|||hey, R937 can you answer my concat null option question ?
(posted recently)|||Originally posted by jacmat
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.
DOB = The field Name
BDAYS = The table name
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30|||Originally posted by r937
select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30
This may not work at the end of the year. It will skip the people born on January.|||your right
he should test on the month and
add an OR for a test on the next year|||Originally posted by Karolyn
your right
he should test on the month and
add an OR for a test on the next year
I guess this should work. BETWEEN is the best way to go
DOB = The field Name
BDAYS = The table name
====================================
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30
===============================|||of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||Even though you have added BETWEEN in the WHERE clause it still would not work!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Not only for January it would not work if the day falls 30 behind the dates
The DateDiff function would return minus values also.
By the way Toronto is nice city. I used live for a year and half. I love that city
Originally posted by r937
of course my query handles the year-end january boundary!!
however, it did have a few typos in it :rolleyes:
and the test has to be between 0 and 30, not just less than or equal to 30
so in the meantime, i have tested it
this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||you're absolutely right, and i am wrong
i withdraw my solutions
not enough coffee, i guess
i do have a solution, which also prints out the actual age of the person as well, but it was done in access, so i would need a few moments to convert it to sql server syntax
see http://www.dbforums.com/showthread.php?threadid=659590
let me know if you want the conversion|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
that is brilliant
it took me about an hour to figure it out and play with it against my test table of birthdays, starting from the innermost function and working out, to see how it works
that formula is fabulous
did you write that?|||Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.
I'm not sure how it works around leap years. The dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate) function is supposed to account for it, but it may still be 1 day off.|||Originally posted by blindman
Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.
It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.or in the bathtub
it works fine around leap years:
...dob... ..today... bdaythisyr adj bdayadjust day
1977-12-09 2004-01-31 2004-12-09 -30 2004-11-09 313
1977-12-10 2004-01-31 2004-12-10 -30 2004-11-10 314
1977-12-11 2004-01-31 2004-12-11 -30 2004-11-11 315
1977-12-29 2004-01-31 2004-12-29 -30 2004-11-29 333
1977-12-30 2004-01-31 2004-12-30 -30 2004-11-30 334
1977-12-31 2004-01-31 2004-12-31 -30 2004-12-01 335
1978-01-01 2004-01-31 2004-01-01 -30 2003-12-02 335
1978-01-02 2004-01-31 2004-01-02 -30 2003-12-03 336
1978-01-03 2004-01-31 2004-01-03 -30 2003-12-04 337
1978-01-04 2004-01-31 2004-01-04 -30 2003-12-05 338
1978-01-05 2004-01-31 2004-01-05 -30 2003-12-06 339
1979-01-10 2004-01-31 2004-01-10 -30 2003-12-11 344
1980-02-04 2004-01-31 2004-02-04 -30 2004-01-05 4
1980-02-05 2004-01-31 2004-02-05 -30 2004-01-06 5
1980-02-06 2004-01-31 2004-02-06 -30 2004-01-07 6
1980-02-28 2004-01-31 2004-02-28 -30 2004-01-29 28 <--
1980-02-29 2004-01-31 2004-02-29 -30 2004-01-30 29 <--
1980-03-01 2004-01-31 2004-03-01 -30 2004-01-31 30 <--
1984-06-14 2004-01-31 2004-06-14 -30 2004-05-15 135|||If you like it, put it on your web page.
That way I'll be able to find it if I ever need it!
Cheers!|||thanks, i will
:cool:|||It will be my greatest honor to have my code engraved in the Book Of Limeback!|||attribution to: blindman
or any other name if you wish (contact me via email)|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
Itz ya birf-day!
Blindman could you help me out plz?
I have applied your coding as:
Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
I get an error message saying,
Syntax error (missing operator) in query expression'Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30'.
What shall I do to correct this? I am writing this in Access and have been told to change GetDate to Date.|||Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.|||Using Access, I prefer to get the next anniversary of a given date using:DateAdd("yyyy",DateDiff("yyyy",[dob],Now())+IIf(Format(Now(),"mmdd")<Format([dob],"mmdd"),0,1),[dob])Once you've got that, you can do simple date compares to get the rows that interest you.
-PatP|||Originally posted by blindman
Is this an Access database or an Access Data Project connected to a SQL Server database?
Access syntax is slightly different than SQL for many functions, including the datepart function.
Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30
...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it?|||Originally posted by hali99
This is an Access database.
I have used the following code posted below and it seems to be working.
SELECT member_name, member_id
from your_table
where join_date <= now() + 20
What do you make of it? I read that as "show me the members that plan to join sometime in the next 20 days" which isn't quite what I think you want! At least if your join_date column shows the date that the member originally joined.
If I read that correctly, I'd use something like:SELECT member_name, member_id
from your_table
where DateAdd("yyyy",DateDiff("yyyy",[join_date],Now())
+ IIf(Format(Now(),"mmdd")<Format([join_date],"mmdd"),0,1),[join_date]) BETWEEN now() -2 AND now() + 20This will show you the members who expired in the last two day and the members that will expire in the next 20 days.
-PatP|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30blindman, somebody emailed me that this isn't working correctly (if you recall, it's on my web site)
i have tested it and have confirmed that it's broken
create table birthdays
( id tinyint not null primary key identity
, birthday datetime
)
insert into birthdays (birthday) values ('1926-12-26')
insert into birthdays (birthday) values ('1927-12-27')
insert into birthdays (birthday) values ('1928-12-28')
insert into birthdays (birthday) values ('1929-12-29')
insert into birthdays (birthday) values ('1930-12-30')
insert into birthdays (birthday) values ('1931-12-31')
insert into birthdays (birthday) values ('1951-01-01')
insert into birthdays (birthday) values ('1952-01-02')
insert into birthdays (birthday) values ('1953-01-03')
insert into birthdays (birthday) values ('1954-01-04')
insert into birthdays (birthday) values ('1955-01-05')
insert into birthdays (birthday) values ('1956-01-06')
insert into birthdays (birthday) values ('1957-01-07')
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
=0
1929-12-29 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 1
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 2
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 3
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 4
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
1951-01-01 00:00:00.000
it seems to mess up on the year boundary|||I'll check it out. Thanks.|||This should work, but slightly differently:set nocount on
create table AnniversaryDates
( id tinyint not null primary key identity
, AnniversaryDate datetime
)
insert into AnniversaryDates (AnniversaryDate) values ('1926-12-26')
insert into AnniversaryDates (AnniversaryDate) values ('1927-12-27')
insert into AnniversaryDates (AnniversaryDate) values ('1928-12-28')
insert into AnniversaryDates (AnniversaryDate) values ('1929-12-29')
insert into AnniversaryDates (AnniversaryDate) values ('1930-12-30')
insert into AnniversaryDates (AnniversaryDate) values ('1931-12-31')
insert into AnniversaryDates (AnniversaryDate) values ('1951-01-01')
insert into AnniversaryDates (AnniversaryDate) values ('1952-01-02')
insert into AnniversaryDates (AnniversaryDate) values ('1953-01-03')
insert into AnniversaryDates (AnniversaryDate) values ('1954-01-04')
insert into AnniversaryDates (AnniversaryDate) values ('1955-01-05')
insert into AnniversaryDates (AnniversaryDate) values ('1956-01-06')
insert into AnniversaryDates (AnniversaryDate) values ('1957-01-07')
declare @.CurrentDate datetime
set @.CurrentDate = '2005-12-28'
select AnniversaryDate,
dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate) as NextAnniversaryDate,
datediff(d, @.CurrentDate, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate)) as DaysToAnniversaryDate
from AnniversaryDates
drop table AnniversaryDates
Using @.CurrentDate allows you to test four consecutive years to verify no problems with leap years, and also allows you to create the formula as a UDF.
