SQL Question

1. Al
Senior Member
Join Date
Dec 1969
Posts
112

## SQL Question

I have a table with data about users and I have to return the next 5 birthdates <BR>from the field BirthDate(SamllDateTime) <BR><BR>I tried with this :<BR>SELECT TOP 2 Users.Name, Users.BirthDate FROM Users <BR>WHERE DATEPART(mm, BirthDate) &#062;= DATEPART(mm, Getdate()) <BR>ORDER BY DATEPART(mm, BirthDate),DATEPART(dd, BirthDate) <BR><BR>But It&#039;s not going to work well. Can anybody help me please.<BR>Thanks in advance.

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Really, just logic...

Your comparisons and ORDER BY aren&#039;t going to work.<BR><BR>Think about it a sec.<BR><BR>Today is June 15.<BR><BR>So you are asking for<BR>WHERE DATEPART(mm, BirthDate) &#062;= DATEPART(mm, Getdate()) <BR>meaning <BR>WHERE the month of the user&#039;s birthday is greater than or equal to June.<BR><BR>First of all, that means you will get people in the result that have a birthday on June 1st. Oops.<BR><BR>And it doesn&#039;t work to change to:<BR><BR><BR>WHERE DATEPART(mm, BirthDate) &#062;= DATEPART(mm, Getdate()) <BR>AND DATEPART(dd, BirthDate) &#062;= DATEPART(dd, Getdate()) <BR><BR>Suppose today is December 31st. And nobody else has a birthday in December. Ummm...what month is greater than December???<BR><BR>Or, even, today is June 30th. Well, people have birthdays in July and later. But because of the second qualifier, the next birthday you&#039;ll find is July 30th! Anybody born earlier in July is out of luck.<BR><BR>So go back to the SQL Server docs for DATEPART and see if some other way exists...<BR><BR>http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/ts_da-db_2mic.htm<BR><BR>HINT: It *still* isn&#039;t going to work if there are fewer than 5 birthdays past the current one in the current year. You may need to do a second query if you find you are getting back fewer than 5 records.<BR><BR>

3. ks
Senior Member
Join Date
Dec 1969
Posts
734

## RE: SQL Question

Get an idea here:<BR>http://www.aspmessageboard.com/forum/databases.asp?M=236108&P=1&F=21

4. ks
Senior Member
Join Date
Dec 1969
Posts
734

## Or Use DateAdd or DateDiff

where Birthday &#060;= DATEADD(day, 5, getdate()) And Birthday &#062;= getdate()

5. Senior Member
Join Date
Dec 1969
Posts
96,118

Unless the person is born *THIS YEAR* after today.<BR><BR>Birthdate is maybe July 3, 1966. <BR><BR>You have to IGNORE the year, whatever you do, when you are doing birthDAY calculations.<BR><BR>Which he was trying to do, using DatePart, but he just chose the wrong first argument(s) to DatePart. He needs dy. <BR><BR><BR>

6. ks
Senior Member
Join Date
Dec 1969
Posts
734

## Oops! Ignore me.

.

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## 14 seconds too late...

...heh! Sorry about that. I should have given you another minute or two.<BR><BR>

8. ks
Senior Member
Join Date
Dec 1969
Posts
734

## What a shame<eop>

.

9. Al
Senior Member
Join Date
Dec 1969
Posts
112