select upcoming birthday?

Meax

i have users Date Of Birth as DOB in sql server table called UsersProfile. Person DOB a 3/13/2009 b 3/13/2009 c 3/25/2009 d 4/2/2009 e 4/15/2009 How to select next(upcoming) birthday from this table? if two persons bday falls on a same date, like Person a and Person b, how to select those two person?

Syed Mehroz Alam

On top of my head, I can think of something like:

Select min(DOB) from UsersProfile where DOB > GetDate()

for getting persons, we can have:

Select Person from UsersProfile where DOB =
( Select min(DOB) from UsersProfile where DOB > GetDate() )

Hope that helps. Regards,

My Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Meax

thnaks for replying, however i am not getting any data with those quaries

Luc Pattyn

Meax wrote:

i have users Date Of Birth

what is it these unborn people are using? :doh:

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Meax

sorry, meant to write 1980

Luc Pattyn

SELECT TOP 1 * FROM UsersProfile WHERE datepart(dy,DOB)>=datepart(dy,now()) ORDER BY datepart(dy,DOB) is what I would try to get the next one. :)

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Meax

what if 2 people has same birthday? like, Person DOB a 3/13/1980 b 3/13/1980 how to do it without writing TOP 2

Luc Pattyn

Hi, Since the first reply suggested something like Select Person from UsersProfile where DOB = ( Select min(DOB) from UsersProfile where DOB > GetDate() ) it must be rather easy to get it like so: Select Person from UsersProfile where DOB = ( whatever it takes to obtain the next birthday ) So I would try: Select Person from UsersProfile where DOB = ( SELECT min(datepart(dy,DOB)) FROM UsersProfile WHERE datepart(dy,DOB)>=datepart(dy, now())) BTW: This is PHP/MySQL code that works for me:

$Query="SELECT \* FROM $table WHERE DAYOFYEAR(birthday)= ".
	"(SELECT min(DAYOFYEAR(birthday)) FROM $table ".
	"WHERE DAYOFYEAR(birthday)>=DAYOFYEAR(now()))";

:)

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Syed Mehroz Alam

Strange.. It works for me. Here are my test statements:

declare @UsersProfile table
(
Person varchar(20),
DOB datetime
)

insert into @UsersProfile
select 'Person1', '2009-03-11'
union select 'Person2', '2009-03-13'
union select 'Person3', '2009-03-13'
union select 'Person4', '2009-03-16'
union select 'Person5', '2009-03-25'

select * from @UsersProfile
where DOB = ( select min(DOB) from @UsersProfile where DOB>GetDate() )

Are you sure your DOB column is of DateTime type? Regards,

My Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Meax

How to make this work in sql server "SELECT * FROM $table WHERE DAYOFYEAR(birthday)= ". "(SELECT min(DAYOFYEAR(birthday)) FROM $table ". "WHERE DAYOFYEAR(birthday)>=DAYOFYEAR(now()))";

Jay Royall

Luc has given you more than enough help, if you can't help yourself from here then maybe you're in the wrong profession?!

Meax

i am just trying to learn

Jay Royall

Sure I realise that, I am too, but the best way to learn isn't to get other people to do it for you. Like I said, Luc has offered a great deal of help here, more than enough for you to work out the rest. If you still can't do it then post your code, I'm sure someone will be able to point you in the right direction.

je_gonzalez

SELECT * FROM UsersProfile WHERE ((403 + (31 * month(DOB) + day(DOB)) - (31 * month(getdate()) + day(getdate()))) % 403) = (SELECT MIN(((403 + (31 * month(DOB) + day(DOB)) - (31 * month(getdate()) + day(getdate()))) % 403)) FROM UsersProfile)