Figuring out your next birthday (SQL)



  • So I am trying to figure out in SQL (SQL Server 2005) how to get the date of your next birthday given your birthdate. I have some SQL code that works, but it is ugly, can anyone see a way to clean this up? Or a better way to do it (SQL is not my strong point).

     Thanks

    <font color="#0000ff" size="2">

    DECLARE<font size="2"> @BirthDate </font><font color="#0000ff" size="2">DATETIME</font><font size="2"> </font>

    </font>

    DECLARE<font size="2"> @BirthDate </font><font color="#0000ff" size="2">DATETIME</font><font size="2"> </font>

    <font size="2"></font>

    <font color="#0000ff" size="2">SET<font size="2"> @BirthDate </font><font color="#808080" size="2">=</font><font size="2"> </font><font color="#ff0000" size="2">'19810409'</font><font size="2"> </font></font><font color="#0000ff" size="2">

    SELECT<font size="2"> </font><font color="#ff00ff" size="2">dateAdd</font><font color="#808080" size="2">(</font><font color="#ff00ff" size="2">year</font><font color="#808080" size="2">,</font><font size="2"> </font><font color="#ff00ff" size="2">convert</font><font color="#808080" size="2">(</font><font color="#0000ff" size="2">int</font><font color="#808080" size="2">,</font><font size="2"> </font><font color="#ff00ff" size="2">Year</font><font color="#808080" size="2">(</font><font color="#ff00ff" size="2">GetDate</font><font color="#808080" size="2">()</font><font size="2"> </font><font color="#808080" size="2">-</font><font size="2"> @Birthdate</font><font color="#808080" size="2">)</font><font size="2"> </font><font color="#808080" size="2">-</font><font size="2"> 1899</font><font color="#808080" size="2">),</font><font size="2"> @Birthdate</font><font color="#808080" size="2">)</font>

    </font>



  • Your SQL works, except for the corner case of Feb. 29th.   It'll return the next year where there IS an actual Feb 29th, which may or may not be what you want.  The SQL below, will back date Feb 29th to Feb 28th on years where there is none.  I think that is what is normal.

     

    -- SQL to create function 

    CREATE FUNCTION NextBirthDay
    (
        @BirthDay DateTime
    )
    RETURNS DateTime
    AS
    BEGIN
        DECLARE @CurrentAge int
        DECLARE @NextBirthDay DateTime

        SET @CurrentAge = DateDiff(year, @BirthDay, GETDATE())

        SET @NextBirthDay = DateAdd(year,@CurrentAge,@Birthday)
                
        IF @NextBirthDay < GetDate()
            SET @NextBirthDay = DateAdd(year,1,@NextBirthDay)

        RETURN @NextBirthDay

    END

     

    -- SQL To Test

    DECLARE @BirthDay DateTime

    SET @BirthDay = '1976-02-29'

    DECLARE @NextBirthDay DateTime

    SET @NextBirthDay = Scratch.dbo.NextBirthDay(@BirthDay)

    SELECT @NextBirthDay



  • Thank you for the response.

    Using your test case '<font color="#ff0000" size="2">1976-02-29'</font><font size="2"> </font><font color="#808080" size="2"></font>in the code I originally posted gives me a results of '2007-02-28 00:00:00.000' which I think is correct (this is SQL Server 2005 if that makes a difference). Thank you though I hadn't considered Feb-29th.

    I just really hate how my code looks. So I'll probably go to yours just because it is more readable.

    Thanks again!!!



  • I'm glad I could help.  The inaccuracy of Feb 29th was probably my bad.  It was probably a test case from one of my intermediate steps and not a result of your original function. 



  • Hi there

    Nice little challenge - I like it. Here is my effort if you're interested ( Sybase T-SQL ). Could convert to stored procedure easily.

    declare @bd datetime, @bdmmdd int, @currmmdd int
    select @bd = '29 feb 1964'
    select @bdmmdd   = datepart(mm,@bd      )*100 + datepart(dd,@bd      )
    select @currmmdd = datepart(mm,getdate())100 + datepart(dd,getdate())

    select convert(datetime, convert(varchar,
    (case when @currmmdd > @bdmmdd then 1 else 0 end + datepart (yy, getdate()) )
    10000 +
     case when @bdmmdd !=229 then @bdmmdd else 228 end ))

    Rgds



  • Postgres 8.1, in one SQL statement (made into a function because it uses the argument four places), correctly handles Feb 29th.  Output:

    pho=> select birthday ('1980-04-09');
      birthday
    ------------
     2007-04-09
    (1 row)

    pho=> select birthday ('1980-02-29');
      birthday
    ------------
     2007-02-28
    (1 row)

    pho=> select birthday ('1981-02-29');
    ERROR:  date/time field value out of range: "1981-02-29"

    (Makes sense, 1981 wasn't a leap year.)

    pho=> select birthday ('1981-12-29');
      birthday
    ------------
     2006-12-29
    (1 row)

    The query:

     create or replace function birthday (date) returns date as $$
    select case
      when this_year.bd < current_date then next_year.bd
      else this_year.bd
      end as birthday
    from
     (select date ($1 -
        (extract (year from $1) - extract (year from current_date)) * interval '1 year') as bd) this_year,
     (select date ($1 -
        (extract (year from $1) - extract (year from current_date) - 1) * interval '1 year') as bd) next_year;
    $$ language sql stable strict;
     



  • A more declarative version:

    create or replace function birthday (date) returns date as $$
    select
      min(bd)
    from
     (select date ($1 -
        (extract (year from $1) - extract (year from current_date)) * interval '1 year') as bd
     union all
     select date ($1 -
        (extract (year from $1) - extract (year from current_date) - 1) * interval '1 year') as bd) dates
    where
      bd > current_date
    $$ language sql stable strict;
     



  • Why do this in SQL?  This is a job for code.  SQL is there to store data.



  • wouldn't it be more simpler to do (this works in mysql but, not 100% sure that it will work with MsSQL. Where's the online documentation for SQL server?) :

    CREATE FUNCTION NextBirthDay(BirthDate Date) RETURNS Date

    BEGIN

        RETURN DATE_ADD(BirthDate, INTERVAL 1 YEAR);

    END;



  • Just relised that it won't work (and can't delete it because of the time limit).

     



  • But this will

    CREATE FUNCTION NextBirthDay(BirthDate Date) RETURNS Date

    BEGIN

        RETURN DATE_ADD(BirthDate, INTERVAL (YEAR(NOW()) - YEAR(BirthDate) + 1) YEAR);

    END;



  • SQL is also used to retrieve relevant data from the database.

    This is part of a bigger where clause, the proc returns a list of people who's birthdays are coming up in the next X days.

    Thanks everyone for the responses!!

     



  • @whalemangler said:

    Why do this in SQL?  This is a job for code.  SQL is there to store data.

    This is not a very well-informed comment. Firstly SQL doesn't store anything; it is a language used to update and retrieve data from a relational database. Secondly, those who think that data should only ever be retrieved from a database as a raw block and processed in some Java middle-tier or front end are missing out on the real power of an RDBMS. Where do I start explaining...?


  • Discourse touched me in a no-no place

    @vr602 said:

    This is not a very well-informed comment.
    Why didn't you reply to this thread just after you first joined instead of waiting over a year?



  • @PJH said:

    @vr602 said:

    This is not a very well-informed comment.
    Why didn't you reply to this thread just after you first joined instead of waiting over a year?

     

    Oh come on! These well informed replies take YEARS to come up with!

    Give the guy a break!



  • @vr602 said:

    Secondly, those who think that data should only ever be retrieved from a database as a raw block and processed in some Java middle-tier or front end are missing out on the real power of an RDBMS.

    You obviously hate hardware vendors. Just imagine the lost sales of all those nice überservers, if people actually start using databass as they are meant to be used, instead of treating them like a fancy file system and reinventing the database functionality in the middle layer.



  • Well I don't hate anyone really. But, like you I suspect, I do pity middle-layer Bean-type thinkers, who never get beyond "select * from table", then complain about performance, locking contention issues, result-set joins, cache syncronisation problems blah blah.  



  • I just had to add my two cents...

     

    <FONT color=#0000ff size=2>DECLARE</FONT><FONT size=2> @birthDate </FONT><FONT color=#0000ff size=2>datetime</FONT></FONT><FONT color=#0000ff size=2>

    DECLARE</FONT><FONT size=2> @currentDate </FONT><FONT color=#0000ff size=2>datetime

    DECLARE</FONT><FONT size=2> @yearDiff </FONT><FONT color=#0000ff size=2>int</FONT></FONT><FONT color=#0000ff size=2>

    DECLARE</FONT><FONT size=2> @calcDate </FONT><FONT color=#0000ff size=2>datetime

    SELECT</FONT><FONT size=2> @birthDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'02-29-1976'</FONT><FONT size=2> </FONT><FONT color=#008000 size=2>-- '06-11-1979'

    </FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> @currentDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>GETDATE</FONT><FONT color=#808080 size=2>()

    </FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> @yearDiff </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>YEAR</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>@currentDate</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>-</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>YEAR</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>@birthDate</FONT><FONT color=#808080 size=2>)

    </FONT><FONT color=#0000ff size=2>

    SELECT</FONT><FONT size=2> @calcDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>DATEADD</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff00ff size=2>year</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @yearDiff</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @birthDate</FONT><FONT color=#808080 size=2>)

    </FONT><FONT color=#008000 size=2>

    -- we'll have the birthday in the current year, if it's already passed then we'll add a year and use next year's date

    </FONT><FONT color=#0000ff size=2>

    IF</FONT><FONT size=2> @calcDate </FONT><FONT color=#808080 size=2><</FONT><FONT size=2> @currentDate

    </FONT><FONT color=#0000ff size=2>

    BEGIN

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> @calcDate </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>DATEADD</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff00ff size=2>year</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> 1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> @calcDate</FONT><FONT color=#808080 size=2>)

    </FONT><FONT color=#0000ff size=2>

    END

    SELECT</FONT><FONT size=2> @calcDate

    </FONT>


  • I know it's a bit late, but here's my attempt (Sybase T/SQL):

    <font color="#0000ff" size="2">SELECT </font><font color="#ff00ff" size="2">DATEADD</font>(year,1+<font color="#ff00ff" size="2">DATEDIFF</font>(month,'19780417',<font color="#ff00ff" size="2">GETDATE</font>())/12,'19780417')

     Returns feb 29s as 28th of the next year if it's not a leap year. (Didn't try it out extensivly though.)


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.