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
 Calculating age using DOB and variable year

Author  Topic 

JimD
Starting Member

2 Posts

Posted - 2013-09-17 : 12:36:29
Hello,

I am trying to replace a hard-coded 'start of term' date with a variable date, in a simple existing query.

The query calculates a student's age (in years), based on the time difference between the student's date of birth, and the current start of term date.

The start of term date is 01/08/YEAR, and obviously the YEAR value changes each year, so this year, start of term date would be 01/08/2013.

I can get the current year by using SELECT year(GETDATE()), and I can do the age calculation to the current date, using this :

Select FLOOR(DATEDIFF(day,'1996/09/26',GETDATE())/365.242199), but I can't figure out how to express the start of term date as a variable year/fixed day/fixed month.

So, the question is : how to combine the variable YEAR with the fixed day and fixed month, so that it can be used in a DATEDIFF expression?

Many thanks.


Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 12:52:38
[code]-- 01/08/YEAR, and obviously the YEAR value changes each year, so this year, start of term date would be 01/08/2013.

DECLARE @Sample TABLE
(
Birthday DATE NOT NULL
);

INSERT @Sample
(
Birthday
)
VALUES ('19940704'),
('19950911'),
('19941010'),
('20120731'),
('20120801'),
('19990221');

-- SwePeso
SELECT BirthDay,
DATEDIFF(YEAR, Birthday, GETDATE()) - (100 * MONTH(Birthday) + DAY(Birthday) - 1) / 801 AS Age
FROM @Sample;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

JimD
Starting Member

2 Posts

Posted - 2013-09-17 : 13:34:34
SwePeso, that works perfectly. Thanks so much!

Jim
Go to Top of Page
   

- Advertisement -