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.
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');-- SwePesoSELECT BirthDay, DATEDIFF(YEAR, Birthday, GETDATE()) - (100 * MONTH(Birthday) + DAY(Birthday) - 1) / 801 AS AgeFROM @Sample;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
JimD
Starting Member
2 Posts |
Posted - 2013-09-17 : 13:34:34
|
SwePeso, that works perfectly. Thanks so much!Jim |
|
|
|
|
|
|
|