trying to get age from date of birth

SELECT [ID_KUNDNR]
      ,CASE
       WHEN DA_FODELSEAR IS NULL THEN 0
       WHEN dateadd(year, datediff (year, DA_FODELSEAR, getdat开发者_如何学JAVAe()), DA_FODELSEAR) > getdate() THEN datediff (year, DA_FODELSEAR, getdate()) - 1
       ELSE datediff (year, DA_FODELSEAR, getdate())
       END As Age
      ,[Catalog]
  FROM Table

I get

Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type datetime.

Any solutions?

I'm just wondering why you are using case. Try the below and see whether it works.

 SELECT [ID_KUNDNR] ,
    datediff (year, 0, (getdate()-DA_FODELSEAR)) as Age,
    Catalog
    From Table

But again are you sure all values of DA_FODELSEAR is convertable to datetime?

If you have trouble try this.

 SELECT [ID_KUNDNR] ,
    Case When ISDATE(DA_FODELSEAR) = 0 Then 0 Else
        datediff (year, 0, (getdate()-DA_FODELSEAR)) 
        End as Age,
    Catalog
    From Table

Incase you want to know, which rows are causing this conversion problem select the table with 'where IsDATE(DA_FODELSEAR) = 0'

You have to make sure that DA_FODELSEAR is in correct datetime format. You can do it by using CAST(DA_FODELSEAR as DATETIME) and check.

I think that your problem lies here dateadd(year, datediff(year, DA_FODELSEAR, getdate()), DA_FODELSEAR). You have a value in your DA_FODELSEAR that is not in a DATETIME format. I would run a SELECT statement to find the offending value. You are checking for NULL but what about a blank space?