October 29, 2001 at 2:53 am
Hi all,
Looking for a way to extract records that have birth or other key dates whose anniversaries are a specified time from today. MY problems have been end of years, leap years and end of months (not so much).
Not sure if this is the right place to post but have had no luck with this type of query so looking everywhere for help...
Thanks all
Rolf
October 29, 2001 at 5:22 am
Take a look at the datediff function, may give you what you need. If not, post more details of what you're trying to do, we'll try to help!
Andy
October 29, 2001 at 7:55 am
I have had a look at the datediff function, but it doesnt quite do what I want.
At the minute I am using this query which extracts all the records with birthdates within this month (but not previous to this DAY of this MONTH) and all those in the next month.
WHERE (DATEPART(m, Birthdate) = DATEPART(m, GETDATE())) AND (DATEPART(d, Birthdate) > DATEPART(d, GETDATE())) OR (DATEPART(m, Birthdate) = DATEPART(m, DATEADD(m, 1, GETDATE())))
What I would like to do is extract all those records that are within a specified DAY length away from the current day. Say today is the 1st of december and a birthday is on ?January 30th, the query above will grab that record, a query that only grabs records that are 40 days away will not.
Any ideas..?
Thanks
Rolf
October 29, 2001 at 11:08 am
If you want all items between today and some sepcified date, then DATEDIFF will work.
This script:
-- today - today's date
-- future date - end of time for which we search.
create table myTest ( me int, bday datetime)
go
insert MyTest select 1, '12/15/60'
insert MyTest select 2, '2/15/70'
insert MyTest select 3, '1/15/80'
insert MyTest select 4, '1/30/65'
insert MyTest select 5, '6/15/72'
go
declare @today datetime
, @future datetime
, @months int
select @months = 2
select @today = '1/16/01'
select @future = dateadd( day, -1 * (day(@today)), dateadd( month, @months, @today))
select *
from MyTest
where dateadd( year, datediff( year, bday, @today), bday) >= @today
and dateadd( year, datediff( year, bday, @today), bday) <= @future
drop table MyTest
Will do the job, unless the dates crosses over the year. I'm sure there is a trick to handle this, but I don't have some now (sorry) to do one. I'll try to fix it later, but this should get you started.
Steve Jones
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply