date format for birthday - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

date format for birthday

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-01-07 : 03:00:47

hi,

i have dateformat smalldatetime
'1975-09-29 00:00:00'
'1952-02-29 00:00:00'

and i would like to get based on this format all people with birthday in past 2 months.

any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 03:11:23

[code]select fields.. FROM YourTable WHERE MONTH(dob)> MONTH(DATEADD(mm,-2,GETDATE())) AND MONTH(dob)<MONTH(GETDATE())[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-07 : 03:43:17

select * from Urtable where dob BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()-60),0)
AND DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-07 : 03:44:32

try this one also
select * FROM urtable WHERE MONTH(empdate)> DATEPART(m,GETDATE())-2 AND MONTH(empdate)<MONTH(GETDATE())

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-01-07 : 16:26:10

hi,

i'm posting some sample code along with your solution, which are in all cases invalid, because all between conditions should not take into consideration year - for we all know that when it comes to birthdays, year is usually not the case, just the right day-month date :)


declare @temp table
(birth smalldatetime)

insert into @temp
select '1975-09-29 00:00:00' union all
select '1952-02-29 00:00:00' union all
select '1982-07-19 00:00:00' union all
select '1946-11-13 00:00:00' union all
select '1967-01-03 00:00:00' union all
select '1992-12-07 00:00:00' union all
select '2008-12-14 00:00:00' 

select * from @temp

--NAGESWAR SOLUTION---
select * from @temp
where birth
 BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()-60),0)
AND DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

--BKLR SOLUTION--
select * from @temp
WHERE MONTH(birth)> DATEPART(m,GETDATE())-2 AND MONTH(birth)<MONTH(GETDATE())


--VISAKH16 SOLUTION--
select * from @temp
WHERE MONTH(birth)> MONTH(DATEADD(mm,-2,GETDATE())) AND MONTH(birth)<MONTH(GETDATE())

thank you for any other solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 16:31:11

See http://www.sqlteam.com/FORUMS/topic.asp?TOPIC_ID=80709

E 12°55'05.63"
N 56°04'39.26"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-01-07 : 16:44:42

Peso, thank you for your script, but fnHasBirthday still does not solve the problem.

best

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 16:45:31

Try again.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 16:47:17

Based on your sample data, I get following resultset

birth		yearsold
1975-09-29	NULL
1952-02-29	NULL
1982-07-19	NULL
1946-11-13	NULL
1967-01-03	42
1992-12-07	16
2008-12-14	NULL

with this statement

SELECT	*,
	dbo.fnHasBirthday(birth, '20081116', '20090109') as yearsold
FROM	@temp

E 12°55'05.63"
N 56°04'39.26"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-01-07 : 16:49:29

:) thank you :) i've seen the changed divisions. :)

best

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 16:49:39

The statement you are looking for is something similar to this

SELECT	*
FROM	@temp
WHERE	dbo.fnHasBirthday(birth, '20081107', '20090107') IS NOT NULL

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources