MS SQL

Problem in finding birthday from date of birth in SQL Server

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2007

Posts: 1

Reputation: winsrividhya is an unknown quantity at this point

Solved Threads: 0

winsrividhya winsrividhya is offline

Offline

Newbie Poster

Problem in finding birthday from date of birth in SQL Server

Feb 13th, 2008

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)))
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

Last edited by peter_budo; Feb 13th, 2008 at 2:27 pm. Reason: Please use [code] tags to separete your DB query from rest of the post

•

Join Date: Feb 2008

Posts: 36

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 12

huangzhi

Offline

Light Poster

Re: Problem in finding birthday from date of birth in SQL Server

Feb 14th, 2008

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

Hence Wijaya
www.ex-Soft.tk

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Problem in finding birthday from date of birth in SQL Server

Feb 21st, 2008

No need for all the separate hits/queries.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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))
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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)))
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)))