How to write a select query that shows upcoming birthdays

  • This will be an easy challenge.

    Select date_of_birth from person

    --This gives values in the YYYYMMDD format like 19700101

    I want a list of people whose birthdays are within the next 30 days

    from today. Can someone construct the SQL for me please ?

  • mw112009 (5/20/2013)


    This will be an easy challenge.

    Select date_of_birth from person

    --This gives values in the YYYYMMDD format like 19700101

    I want a list of people whose birthdays are within the next 30 days

    from today. Can someone construct the SQL for me please ?

    Would help to know more. What is the data type of the column date_of_birth?

  • The type of the date_of_birth column is char(8)

  • HOPE THIS HELP

    DECLARE @TABLE_NAME TABLE(

    NAME VARCHAR(30),

    BDAY CHAR(8))

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('A','20130522')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('B','20130523')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('C','20130524')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('D','20130525')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('E','20130521')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('F','20130620')

    SELECTNAME,

    DATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) [BDAY WITHIN 30 DAYS]

    FROM@TABLE_NAME

    WHEREDATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) BETWEEN 1 AND 30

  • eklavu (5/21/2013)


    HOPE THIS HELP

    DECLARE @TABLE_NAME TABLE(

    NAME VARCHAR(30),

    BDAY CHAR(8))

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('A','20130522')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('B','20130523')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('C','20130524')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('D','20130525')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('E','20130521')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('F','20130620')

    SELECTNAME,

    DATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) [BDAY WITHIN 30 DAYS]

    FROM@TABLE_NAME

    WHEREDATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) BETWEEN 1 AND 30

    This will work only if the year of birth is the current year

    It will not work for dates like 19700101

    Edited:Corrected some spelling mistakes


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • One way to do this

    DECLARE@person TABLE

    (

    person_name VARCHAR(50),

    date_of_birth CHAR(8)

    )

    DECLARE@start_date CHAR(4)

    DECLARE @end_date CHAR(4)

    INSERT@person

    SELECT 'A', '19800101' UNION ALL

    SELECT 'B', '19700605' UNION ALL

    SELECT 'C', '19650525' UNION ALL

    SELECT 'D', '19901020'

    SET@start_date = RIGHT(CONVERT(CHAR(8),CURRENT_TIMESTAMP,112),4)

    SET@end_date = RIGHT(CONVERT(CHAR(8),DATEADD(DAY,29,CURRENT_TIMESTAMP),112),4)

    SELECT*

    FROM@person

    WHERERIGHT(date_of_birth,4) BETWEEN @start_date AND @end_date


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • First, run the following query against your data:

    select * from dbo.yourTableNameHere where isdate(date_of_birth) = 0;

    If this returns any records you have dates that aren't dates and those need to be fixed.

  • Then try this when all the dates are valid:

    DECLARE @TABLE_NAME TABLE(

    NAME VARCHAR(30),

    BDAY CHAR(8))

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('A','19901022')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('B','19940523')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('C','19980602')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('D','19590525')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('E','20130521')

    INSERT INTO @TABLE_NAME(NAME,BDAY)

    VALUES('F','20130620');

    select

    *

    from

    @TABLE_NAME

    where

    cast(BDAY as DATE) between dateadd(year, - datediff(year, cast(BDAY as DATE), getdate()), cast(getdate() as DATE))

    and dateadd(year, - datediff(year, cast(BDAY as DATE), getdate()), dateadd(day, 30, cast(getdate() as DATE)));

    GO

  • I couldn't see any difference in CPU or Query cost between my answer and Lynn's answer when doing <10 records.

    It's all down to preference.

    DECLARE @FictionalDate DATE = '1969-12-15'

    DECLARE @bdays TABLE (Birthday CHAR(8))

    INSERT @bdays

    (Birthday)

    VALUES ('20010101'),

    ('19700102'),

    ('19700101'),

    ('19951201'),

    ('20130521'),

    ('20130620')

    SELECT CONVERT(DATE,Birthday)

    FROM @bdays

    WHERECONVERT(INT,CONVERT(DATETIME,Birthday)) - CONVERT(INT,CONVERT(DATETIME,@FictionalDate)) <= 30

  • Edit: Copied wrong data to this post.

  • chris.rogers00 (5/21/2013)


    I couldn't see any difference in CPU or Query cost between my answer and Lynn's answer when doing <10 records.

    It's all down to preference.

    DECLARE @FictionalDate DATE = '1969-12-15'

    DECLARE @bdays TABLE (Birthday CHAR(8))

    INSERT @bdays

    (Birthday)

    VALUES ('20010101'),

    ('19700102'),

    ('19700101'),

    ('19951201'),

    ('20130521'),

    ('20130620')

    SELECT CONVERT(DATE,Birthday)

    FROM @bdays

    WHERECONVERT(INT,CONVERT(DATETIME,Birthday)) - CONVERT(INT,CONVERT(DATETIME,@FictionalDate)) <= 30

    Actually, your code doesn't work. Run the following:

    DECLARE @FictionalDate DATE = getdate() --'1969-12-15'

    DECLARE @bdays TABLE (Birthday CHAR(8))

    INSERT @bdays

    (Birthday)

    VALUES ('20010101'),

    ('19700102'),

    ('19700101'),

    ('19951201'),

    ('20130521'),

    ('20130620')

    SELECT CONVERT(DATE,Birthday)

    FROM @bdays

    WHERECONVERT(INT,CONVERT(DATETIME,Birthday)) - CONVERT(INT,CONVERT(DATETIME,@FictionalDate)) <= 30

    Here are the results I get:

    (No column name)

    2001-01-01

    1970-01-02

    1970-01-01

    1995-12-01

    2013-05-21

    2013-06-20

    Remember, the requirement was to show who was having a birthday within the next 30 days.

  • Yeah, my bad.. Should be BETWEEN 0 AND 30

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply