Problem in finding birthday from date of birth in SQL Server

[winsrividhya]

I have a database in SQL Server 2005.I've a table namely 'PersonalDetails' in it which contains a 'date of birth' field of type datetime.I've to generate weekly birthday reports .
I wrote a sp like this:

ALTER PROCEDURE [dbo].[selectweekfrompersonaldetails]

	(
	@paramdate datetime ='02/01/2007'
	
	)
	as 
--	select EmpID,EmpName,month(DateOfBirth) bmon,day(DateOFBirth) bday from PersonalDetails where (day(dateofbirth) between day(@paramdate1) and day(@paramdate1+7)) and month(dateofbirth)=month(@paramdate1)
select EmpID,EmpName,month(dateofbirth) bmon,day(dateofbirth) bday from PersonalDetails where (day(dateofbirth) >= day(@paramdate) and day(dateofbirth)<=day(dateadd(dd,7,@paramdate)))---and(month(dateofbirth)=month(@paramdate) or  month(dateofbirth)=month(dateadd(dd,7,@paramdate)))

but it does not work.
Can u guide me in this

[huangzhi]

Try this code

select 1 as EmpID, 'John' as EmpName, '2007/01/01' as DateOfBirth
into #tmpData
union all
select 2 as EmpID, 'Anne' as EmpName, '2007/01/05' as DateOfBirth
union all
select 3 as EmpID, 'Tony' as EmpName, '2007/01/08' as DateOfBirth
union all
select 4 as EmpID, 'Erin' as EmpName, '2007/02/28' as DateOfBirth
union all
select 5 as EmpID, 'Jane' as EmpName, '2007/03/01' as DateOfBirth

declare @paramdate datetime

select @paramdate = '2006/02/25'

select EmpID, EmpName, month(dateofbirth) bmon,day(dateofbirth) bday,
DateOfBirth,
cast(cast(year(DateOfBirth) as varchar) + '/' + cast(month(@paramDate) as varchar)+ '/' + cast(day(@paramDate) as varchar) as smalldatetime)
from #tmpData
where DateOfBirth between cast(cast(year(DateOfBirth) as varchar) + '/' + cast(month(@paramDate) as varchar)+ '/' + cast(day(@paramDate) as varchar) as smalldatetime) and cast(cast(year(DateOfBirth) as varchar) + '/' + cast(month(@paramDate) as varchar)+ '/' + cast(day(@paramDate) as varchar) as smalldatetime) + 7

drop table #tmpData

[SheSaidImaPregy]

No need for all the separate hits/queries.

SELECT EmpID, EmpName, DATEPART(m, DateOfBirth) As bmon, DATEPART(d, DateOfBirth) As bday FROM PersonalDetails WHERE DATEPART(d, DateOfBirth) BETWEEN DATEPART(d, @paramdate1) AND DATEPART(d, (DATEPART(d, 7, @paramdate1)))

or

SELECT EmpID, EmpName, month(DateOfBirth) As bmon, day(DateOfBirth) As bday FROM PersonalDetails WHERE day(DateOfBirth) BETWEEN day(@paramdate1) AND day(DATEADD(d, 7, @paramdate1))

You need to be relatively careful of this method because it is very slow. If you have a lot of records, this task can take a very long time.

The reason is that whenever you use formatting within the WHERE clause, it formats EVERY record, then does the search. Therefore if you have 2,000 records, it will format every single one, for every single where part (where in here you have 3 formats, so it will format it 6,000 times).

To make this less and quicker, one at least add an Index on these fields (DateOfBirth), and then eliminate not possible candidates. So if you have candidates that you do not want (Like inactive employees), add a where clause in the BEGINNING, which will reduce the number of records formatted. Like below:

SELECT EmpID, EmpName, DATEPART(m, DateOfBirth) As bmon, DATEPART(d, DateOfBirth) As bday FROM PersonalDetails WHERE EmpActive=1 AND DATEPART(d, DateOfBirth) BETWEEN DATEPART(d, @paramdate1) AND DATEPART(d, (DATEPART(d, 7, @paramdate1)))