CODEWITHSUNDEEP
sql-server-2005
vovkjn
vovkjn

Reputation: 99

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, getdate()), 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?

Upvotes: 0

Views: 867

Answers (3)

Neil Knight
Neil Knight

Reputation: 48597

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?

Upvotes: 0

The King
The King

Reputation: 4654

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'

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55529

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.

Upvotes: 0

Related Questions