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:
SELECT myfriends.* FROM user AS myself INNER JOIN 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...
UPDATE user SET next_birthday = DATEADD(YEAR, DATEDIFF(YEAR, birthday, GETDATE()) + 1, birthday) WHERE 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)