SQL Find Age using DateDiff

Question

Fields        || Data

ID            || V465

DOB           || 1946-09-05

DATE_OF_DEATH || 1974-05-11

I am using this SQL but I am getting an error.

select DATEDIFF("YYYY",'DOB','DATE_OF_DEATH') where ID= 'V465'

Its SQL SERVER Management Studio R2 and

Error: Msg 207, Level 16, State 1, Line 2 Invalid column name 'ID'

In SQL Server the single quote is a text delimiter - maybe you meant to use [DOB], [DATE_OF_DEATH]. — rheitzman, Jan 29 '14 at 20:40
Seriously? Do you expect SQL Server to guess which table you want to query from? — Aaron Bertrand, Jan 29 '14 at 21:59

score 5 · Answer 1 · edited May 23 '17 at 10:30

5

You forgot the FROM (and surrounded your column names with single quotes which treats them as string literals):

select DATEDIFF("YYYY",DOB,DATE_OF_DEATH) 
FROM {tablename}
where ID= 'V465'

And DATEDIFF alone is not the right way to determine "age" since

DATEDIFF("yyyy",'2001-12-01','2003-01-31')

will give you 2 instead of 1.

See one method here and another here

edited May 23 '17 at 10:30

Community

1
1

answered Jan 29 '14 at 20:54

D Stanley

149,601
11
178
240

I also wouldn't use `"YYYY"`... but rather `YEAR` (more explicit, since for example `Y` doesn't do what you think), and I see no need for the double quotes at all. – Aaron Bertrand Jan 29 '14 at 22:00

IgorM · Answer 2 · 2014-01-29T20:57:11.123

1

SELECT DATEDIFF(YY,'01-01-2001','12-31-2002')

Returns 1 on MSSQL

edited Jan 29 '14 at 20:57

answered Jan 29 '14 at 20:49

IgorM

1,348
1
12
28

score 1 · Answer 3 · answered Jan 29 '14 at 20:55

1

that error most likely comes from not including a table in your select statement

select DATEDIFF(YY,DOB,DATE_OF_DEATH) AS AGE
  FROM TABLE_NAME 
 where ID= 'V465'

answered Jan 29 '14 at 20:55

Bozman

477
4
16

score 0 · Answer 4 · answered Jan 29 '14 at 21:47

Test Data

DECLARE @TABLE TABLE(ID  VARCHAR(20),DOB DATE, DATE_OF_DEATH DATE)
INSERT INTO @TABLE VALUES
('V465', '1946-09-05', '1974-05-11'),('V466', '1945-09-05', '2000-11-11'),
('V467', '1982-09-05', NULL),('V468', '1946-09-05', NULL)

Query

SELECT DATEDIFF(YEAR,DOB, COALESCE(DATE_OF_DEATH, GETDATE())) AS AGE
FROM @TABLE

Using COALESCE function if someone hasnt died yet you can calculate their age too. COALESCE function will take difference from date of death and if the column is null it will take difference from today's date. and will give you the following result.

Result

╔═════╗
║ AGE ║
╠═════╣
║  28 ║
║  55 ║
║  32 ║
║  68 ║
╚═════╝

SQL Find Age using DateDiff

4 Answers4