Post reply

Need Quick Answer---Calc Date for Under 18

  • March 21, 2006 at 4:22 pm

    #365575

    I have a DOB field and I need to filter out anyone that 17 and younger.  How would I do that.  I have tried unsuccessfully.  I am sure its a DATEADD -18 or something or a Datediff of DOB and Today...Just not sure.

     

    Thanks in advance

     

  • March 21, 2006 at 5:31 pm

    #627998

    Ok here is a quick solution, no guarantee that it's the best or most efficient 

    declare @dte datetime
declare @dob datetime
    set @dte = '23 Mar 2006'
set @dob = '23 Mar 1989'
    SELECT ((((YEAR(@dte) * 365) + DAY(@dte)) - ((YEAR(@dob) * 365) + DAY(@dob))) / 365)

     

    --------------------
    Colt 45 - the original point and click interface

  • March 22, 2006 at 9:56 am

    #628155

    I think this does it.

    declare @birth smalldatetime

    declare @now smalldatetime

    select @birth = '3/22/1988'

    -- Gets todays date without the time

    select @now = convert(varchar(12),getdate(),101)

    if (dateadd(yy,-18,@now) >= @birth)

      select 'older than 18'

    else

      select 'younger than 18'

  • March 23, 2006 at 7:55 am

    #628342

    select * from yourTable

    where dateadd(yy,18,convert(varchar(10),dob,112)) < getdate()

    Nigel
    How to post questions

  • March 23, 2006 at 9:58 pm

    #628512

    if you want more accurate calculation use this

    Person born on 2005-05-01 and is not consider one year old until 2006-05-01

    select dob, dob_this_year, age = case when (getdate() > dob_this_year) then

    datediff(year, dob, getdate())

    else

    datediff(year, dob, getdate()) - 1

    end

    from

    (

    select dob, dob_this_year = dateadd(year, year(getdate()) - year(dob), dob)

    from

    (

    select convert(datetime, '2005-05-01') as dob union all

    select convert(datetime, '1987-03-15') as dob union all

    select convert(datetime, '1987-05-15') as dob union all

    select convert(datetime, '1988-03-15') as dob union all

    select convert(datetime, '1988-05-01') as dob

    ) a

    ) b

    RESULT

    DOB DOB This Year Age

    2005-05-01 2006-05-01 0

    1987-03-15 2006-03-15 19

    1987-05-15 2006-05-15 18

    1988-03-15 2006-03-15 18

    1988-05-01 2006-05-01 17

  • March 24, 2006 at 3:11 am

    #628535

    Here's yet another variation on the age theme.

    -- Determining current age notes.

    -- To be able to determine current age based on DOB date, you first must

    -- decide which way to round if the birthday is today.

    -- If the new year should be counted, round up (ceiling),

    -- if the new year should be counted tomorrow, round down (floor)

    -- current age rounded down

    declare @dob char(8)

    set @dob = '19620311'

    select floor(datediff(day, @dob, getdate()) / 365.25)

    go

    -- rounded up

    declare @dob char(8)

    set @dob = '19620311'

    select ceiling(datediff(day, @dob, getdate()) / 365.25)

    go

    /Kenneth

Viewing 6 posts - 1 through 5 (of 5 total)

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