select upcoming birthday?
-
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?
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
-
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
-
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?
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 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
-
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?
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
-
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
-
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
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
-
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
-
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
-
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()))";Luc has given you more than enough help, if you can't help yourself from here then maybe you're in the wrong profession?!
-
Luc has given you more than enough help, if you can't help yourself from here then maybe you're in the wrong profession?!
-
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.
-
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?
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)