select birthday upcoming month

I want to display birthday list upcoming month.

The birthdates are stored in the birthday column . How can I display this ?


Select birthdaycolumn from yourtable where MONTH(birthdaycolumn) = MONTH(GETDATE()) 

try this shall work

My preferred solution is to have an extra column that you can index; next_birthday.

As an overnight process you update that column to ensure the value is always in the future.

This allows queries such as the following:

  user               AS myself
  user               AS myfriends
    ON  myfriends.next_birthday >=                   myself.next_birthday
    AND myfriends.next_birthday <  DATEADD(MONTH, 1, myself.next_birthday)

This keeps the year part of the datetime field, and so cope with going around the corner very well: - If my next birthday is 2012-12-15 - I want birthdays in the last half of Dec and birthdays in the first half of Jan - Solutions that use Date and Month only (not year) struggle with this

The field can be indexed and so the solution does not require a scan of every record in your table.

It does, however, assume that you can run that maintenance job to update the field. Where the job has not executed, a portion of the data will be out of date.

You also need to be careful of people with a birthday on 29th Feb - you can't just keep adding one year to their birthday; 29th Feb + 1 year moves to 28th feb, and no matter how many years you add, you never get back to 29th Feb. But that's not too hard...

  next_birthday = DATEADD(YEAR, DATEDIFF(YEAR, birthday, GETDATE()) + 1, birthday)
  next_birthday < GETDATE()

All of this is also very narrow in scope. It introduces a new column and a new maintenance job, for one very particular need. The question should then be asked; Are those costs preferable to the alternative queries (which often require table scans, etc)?

try this one

Select * 
from yourDataTable
where MONTH(birthdaycolumn) = (MONTH(GETDATE() + 1)) 

Try something like this:

SELECT BirthDay 
FROM TableName
WHERE DATEPART(m, BirthDay) = UpcomingMonth 

Use UpcomingMonth as parameter in this SQL query. Hope this helps.

Resolved that problem.

select * from Employee

where convert(nvarchar,convert(datetime,convert(nvarchar,YEAR(GETDATE()))+'.'+convert(nvarchar,MONTH(Birthday))+'.'+convert(nvarchar,DAY(Birthday))),102) between convert(nvarchar,getdate(),102) and convert(nvarchar,DATEADD(D,14 ,GETDATE()),102)

Need Your Help