Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Selecting people born between specified day & month regardless of year

Posted on 2006-04-22
10
Medium Priority
?
407 Views
Last Modified: 2009-12-16
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
0
Comment
Question by:stu_bill
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16515890
try this

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

Author Comment

by:stu_bill
ID: 16515938
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16515993
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:stu_bill
ID: 16516026
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16520058
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
 
LVL 35

Expert Comment

by:James0628
ID: 16521345
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
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 16521367
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16523504
did mine actually work?
0
 

Author Comment

by:stu_bill
ID: 16524241
sorry, no it didnt.
0
 
LVL 35

Expert Comment

by:James0628
ID: 16532708
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question