format for a date - SQL Server Forums

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;

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources