Need Quick Answer---Calc Date for Under 18

• wilson-213647

  SSC Enthusiast

  Points: 112

  More actions

  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

   

• philcart

  SSC-Forever

  Points: 47794

  More actions

  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

• Chris Castle

  Old Hand

  Points: 381

  More actions

  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'

• nigel.

  SSChampion

  Points: 11636

  More actions

  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

• [KH]

  SSCommitted

  Points: 1908

  More actions

  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

  selectdob, dob_this_year, age = case when (getdate() > dob_this_year) then

  datediff(year, dob, getdate())

  else

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

  end

  from

  (

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

  from

  (

  selectconvert(datetime, '2005-05-01') as dobunion all

  selectconvert(datetime, '1987-03-15') as dobunion all

  selectconvert(datetime, '1987-05-15') as dobunion all

  selectconvert(datetime, '1988-03-15') as dobunion all

  selectconvert(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

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  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