SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculating age using DOB and variable year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JimD
Starting Member

United Kingdom
2 Posts

Posted - 09/17/2013 :  12:36:29  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 09/17/2013 :  12:52:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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;



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

JimD
Starting Member

United Kingdom
2 Posts

Posted - 09/17/2013 :  13:34:34  Show Profile  Reply with Quote
SwePeso, that works perfectly. Thanks so much!

Jim
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000