0

I wrote the SQL below to get a person's age based on the Date of Birth (DOB) field on Client table. However, I got an error message: Adding a value to a 'datetime2' column caused an overflow. Can someone help me to fix it? Thanks,

 SELECT YEAR(DATEADD(DAY, DATEDIFF(day,CLIENT.DOB,'2017-01-01'),
 CAST('0001-01-01' AS DATETIME2)))-1 AS AGE 
 FROM CLIENT
Siyual
  • 16,415
  • 8
  • 44
  • 58
Ice
  • 429
  • 2
  • 6
  • 19
  • 5
    That calculation does not make sense. – Gordon Linoff Jan 27 '17 at 21:45
  • 1
    For any date after `2017-01-01` you're adding a negative number of days to `0001-01-01`, which overflows `DATETIME2` because `0001-01-01` is the earliest date that will fit into `DATETIME2`. – Hart CO Jan 27 '17 at 21:51
  • Maybe do it like [this](http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate), or one of the many other ways. Just be sure to check if their birthday has occurred yet this year. – S3S Jan 27 '17 at 21:56
  • Yeah, you could use `GETDATE()` in place of hard-coded `2017-01-01` but would still have to make sure no future dob's snuck into your data. – Hart CO Jan 27 '17 at 21:58

0 Answers0