Datetime comparison

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  October 12, 2009 at 7:24 am

  #134299

  This query is producing the following error

  Select Top 10 Firstname, Middlename, surname, year(convert(varchar,dateofbirth,103)) - year(convert(varchar,Getdate(),103)) from biodata

  Msg 242, Level 16, State 3, Line 1

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  ANY HELP

  kshitij kumar
  kshitij@krayknot.com
  www.krayknot.com

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 12, 2009 at 7:34 am

  #1064718

  Why are you converting to a varchar ? varchar(what) by the way ? if you dont specify it defaults to 1

  select Top 10 Firstname, Middlename, surname, year(dateofbirth) - year(Getdate()) from

  biodata

  This wont calculate an age correctly, try...

  select CASE

  WHEN dateadd(year, datediff (year, d1, d2), d1) > d2

  THEN datediff (year, d1, d2) - 1

  ELSE datediff (year, d1, d2)

  END as Age

  ---

  
  Clear Sky SQL
  My Blog[/url]