Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 format for a date

Author  Topic 

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2015-03-03 : 08:57:01
I am trying to calculate age from a date that is short. I have 5.5 million rows and the format looks like this 1945-01-01. I have my query which is:

select mcrmain.dateofbirth,
day(dateofbirth)as d,
month(dateofbirth) as m,
year(dateofbirth)as y, year('1/1/2015') - year(dateofbirth) as age
from anthemq.dbo.MCRMain;

Is the '1/1/2015' the correct way to format if I want a person's age as of 1/1/2015? The output looks like this:

dateofbirth d m y age
1946-04-13 13 4 1946 69
1946-04-13 13 4 1946 69
1946-04-13 13 4 1946 69
1947-03-31 31 3 1947 68
1947-03-31 31 3 1947 68

tina m miller

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-03 : 10:17:15
The ISO string format will always convert regardless of local settings.


--*** Test Data ***
CREATE TABLE #t
(
dob date NOT NULL
);
INSERT INTO #t
VALUES ('19460413'), ('19470331');
--*** End Test Data ***

select * from #t;

DECLARE @today date ='20150101';

SELECT dob
,CASE
WHEN DATEADD(year, DATEDIFF(year, dob, @today), dob) > @today
THEN DATEDIFF(year, dob, @today) - 1
ELSE DATEDIFF(year, dob, @today)
END AS AgeAsOf@today
FROM #t;

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-10 : 02:22:36
Also make sure to read this http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -