Selecting people born between specified day & month regardless of year

Hi i have a database with a table called tblPersonCore from which i want to select people with a DOB that matches a day and month criteria regardless of the year e.g. i want to select everybody with a birthday between 15th January and 14th March inclusive.  I have been trying the following SQL but no records are returned even with though there are people with birthdays in that range.  I'm obviously doing something wrong but havent worked out what!  

select * into #temp1
from tblpersoncore
where datepart(dd,dob)>=15 and datepart(mm,dob)>=1

select * into #temp2
from tblpersoncore
where datepart(dd,dob)<=14 and datepart(mm,dob)<=3

select * from tblpersoncore
where personid in (select personid from #temp1) and personid in (select personid from #temp2)

drop table #temp1
drop table #temp2
stu_billAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
try this

SELECT * from @tblpersoncore
WHERE CONVERT(varchar(5),dob,103) between '15/01' AND '14/03'
0
stu_billAuthor Commented:
Hi aneeshattingal

Thanks for the comment.  I have tried your suggestion but still no records are returned.  I can see the logic in your solution and expected it to work but unfortunately nothing!  Also, can you see a reason for my (rather long) attempt wouldnt work?
0
Aneesh RetnakaranDatabase AdministratorCommented:
select * into #temp1
from tblpersoncore
where datepart(dd,dob)>=15 and datepart(mm,dob) >=1 and datepart(mm,dob) <=3


union

select * into #temp1
from tblpersoncore
where datepart(dd,dob)<=14 and datepart(mm,dob)<=3



select * into #temp1



declare @test table ( i int identity ,bday datetime)
insert into @test SELECT '01/Jan/2001'
insert into @test SELECT '02/Feb/2002'
insert into @test SELECT '03/Mar/2003'
insert into @test SELECT '04/Apr/2004'
insert into @test SELECT '05/May/2005'

SELECT * from @test
WHERE CONVERT(varchar(5),bday,103) between '01/01' AND '05/03'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stu_billAuthor Commented:
Executing:

declare @test table ( i int identity ,bday datetime)
insert into @test SELECT '01/Jan/2001'
insert into @test SELECT '02/Feb/2002'
insert into @test SELECT '03/Mar/2003'
insert into @test SELECT '04/Apr/2004'
insert into @test SELECT '05/May/2005'

SELECT * from @test
WHERE CONVERT(varchar(5),bday,103) between '01/01' AND '05/03'

Returns:

1      2001-01-01 00:00:00.000
2      2002-02-02 00:00:00.000
3      2003-03-03 00:00:00.000
4      2004-04-04 00:00:00.000

The 4th row returned is 4th April which is outside the criteria: between '01/01' AND '05/03' (assuming dd/mm/yyy)


Why would this happen?
0
LowfatspreadCommented:
SELECT *
from (select x.* ,month(dob) as Dobmth, Datepart(dd,dob) as bobday from tblpersoncore as x
           where month(dob) < 4) as y
where (dobmth = 2)
    or   (dobmth=1 and dobday >= 15)
    or   (dobmth=3 and dobday <= 14)
0
James0628Commented:
I think the problem with your initial code is that you use an AND in the final WHERE.  #temp1 only has days >= 15 and #temp2 only has days <= 14.  Obviously no date is going to fit both criteria, so when you look for dates that are in _both_ #temp1 and #temp2, you find nothing.  I haven't thought this through too carefully, but I think simply changing to an OR will fix it:

select * from tblpersoncore
where personid in (select personid from #temp1) OR personid in (select personid from #temp2)


 FWIW, I would use a method like the one aneeshattingal posted, but you need to use a date format that has the month first for the comparison to work.

SELECT * from @tblpersoncore
WHERE CONVERT(varchar(5),dob,101) between '01/15' AND '03/14'

 (Format 101 instead of 103)

 James
0
James0628Commented:
Oh, and ...

 > 4     2004-04-04 00:00:00.000
 >
 > The 4th row returned is 4th April which is outside the criteria:
 > between '01/01' AND '05/03' (assuming dd/mm/yyy)
 >
 > Why would this happen?

 That's why you need the month first.  You're comparing strings here, not dates.  You may read '05/03' as March 5th, but to the computer, it's just a string of characters.  Anything starting with '04' is less than '05/03', as far as the computer is concerned.  So you need to put the month first for the comparison to work.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
did mine actually work?
0
stu_billAuthor Commented:
sorry, no it didnt.
0
James0628Commented:
Lowfatspread,

 FWIW, the main problem I see with the code you posted is that you can't use aliases (eg. dobmth) in a WHERE clause.  If you used the date functions instead (eg. month(dob)), it might have worked (apart from a couple typos).


 stu_bill,

 Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.