Get current year's birthday in SQL

I have the date of birth (2/3/1967) and I want to convert that to this years birthday.

The select statement would return something开发者_开发问答 like this

userid   date_of_birth  current_bday
abc123    2/3/1967       2/3/2011

I tried playing around with datepart to get month and day but didn't succeed.

SQL Server 2008 R2

---

Here's one way to do it:

Use a DATEADD function to add (in years) the difference between the current year and the birth year (to the birth date)

SELECT userid,
       date_of_birth,
       DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,date_of_birth), date_of_birth) AS current_bday
FROM Users

One thing to worry about is trying to create a date by the individual month and day with the current year. The one problem with that would be trying to create a February 29th birthdate on a year the isn't a leap year. I did test this, and it appears that you will need to specifically account for this, since the DATEADD function gives a date of '2011-02-28' for a birthdate of '2000-02-29'

---

i think this is more neat:

SELECT userid
      ,date_of_birth
      ,DATEADD(YEAR,DATEDIFF(YEAR,date_of_birth,SYSDATETIME()),date_of_birth) 
       AS current_bday 

---

If you want to grab the "next birthday" from today (not just this year, but possibly next year if the birthday was before today, in this year), you need additional checks.

Here's an example which computes the next birthday for each 29th day of each month in the year 1988 in two ways: - no check if the bday has come to pass (bday) - check if bday has come to pass (bday2)

with dobs (dob) as (
    select convert(datetime,dob)
    from (values
        ('1988-01-29')
    ,   ('1988-02-29')
    ,   ('1988-03-29')
    ,   ('1988-04-29')
    ,   ('1988-05-29')
    ,   ('1988-06-29')
    ,   ('1988-07-29')
    ,   ('1988-08-29')
    ,   ('1988-09-29')
    ,   ('1988-10-29')
    ,   ('1988-11-29')
    ,   ('1988-12-29')
    ) as X(dob)
) 
select
    dob,
    dateadd(YY, datepart(YYYY, getdate()) - datepart(YYYY,dob), dob) as bday1
,   case when
        datepart(month, dateadd(year,datediff(year,dob,getdate()),dob))
        <
        datepart(month, getdate())
    or  (   datepart(month, dateadd(year,datediff(year,dob,getdate()),dob))
            <
            datepart(month, getdate())
        and
            datepart(day, dateadd(year,datediff(month,dob,getdate()),dob))
            <
            datepart(day, getdate())
        )
    then dateadd(year,1+datediff(year,dob,getdate()),dob)
    else dateadd(year,datediff(year,dob,getdate()),dob)
    end as bday2
from dobs