CODEWITHSUNDEEP
sqlsql-server
compcobalt
compcobalt

Reputation: 1362

GET Persons Birthday, Days Left Until Bday, and Persons Age this year in MS SQL 2012

The script I have below works great for displaying birthdays but it will not display the birthday on the birthday date and also how do I get the day's left for that person's birthday ? and finally how old will the person be on this birthday.

THE SCRIPT IS:

SELECT  *
FROM    DRIVERS e
WHERE   1 = CASE WHEN MONTH(GETDATE()) < MONTH(DATEADD(WK, 
                                       DATEDIFF(WK, 0, GETDATE())+1,-1))
THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE()) + 1
            AND DAY(DOB) >= 1
            AND DAY(DOB) < DAY(DATEADD(WK,
                                 DATEDIFF(WK, 0, GETDATE())
                                       + 1, -1)) THEN 1 
    WHEN MONTH(DOB) = MONTH(GETDATE())
            AND DAY(DOB) >= DAY(GETDATE())
            AND DAY(DOB) <= DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))) THEN 1

       ELSE 0 END

ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())
            AND DAY(DOB) >= DAY(GETDATE())+1
            AND DAY(DOB) < DAY(DATEADD(WK,
                                 DATEDIFF(WK, 0, GETDATE())
                                       + 1, -1)) THEN 1
       ELSE 0
  END
END

--//    So I need the following:
--//    1. Show birthday on birthday date.
--//    2. Show Days Left until Birthday.
--//    3. Show Age of Person on upcoming birthday.

Thanks so much for any help with this.

Upvotes: 0

Views: 4047

Answers (2)

compcobalt
compcobalt

Reputation: 1362

SELECT 
FLOOR(DATEDIFF(DAY, GETDATE(), DATEADD(YEAR,DATEDIFF(YEAR, DOB, GETDATE()), DOB))) AS BDAYin
, FLOOR(DATEDIFF(dd,DOB, GETDATE()+7) / 365.25) AS AGEwillTurn
FROM DRIVERS 
WHERE 1 = (FLOOR(DATEDIFF(dd,DOB,GETDATE()+7) / 365.25))-(FLOOR(DATEDIFF(dd,DOB,GETDATE()) / 365.25))

Upvotes: 0

jpw
jpw

Reputation: 44891

Try this for age:

CASE 
  WHEN DATEPART(DAYOFYEAR, DOB) < DATEPART(DAYOFYEAR, GETDATE()) 
    THEN DATEDIFF(YEAR, DOB, GETDATE()) 
  ELSE 
    DATEDIFF(YEAR, DOB, GETDATE())-1 
END

and this for days until birthday:

DATEDIFF(DAY, GETDATE(), DATEADD(YEAR,DATEDIFF(YEAR, DOB, GETDATE()), DOB))

It might need some tweaking, but showed correct results when I did some basic tests.

Upvotes: 4

Related Questions