Using WHERE on calculated column data

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

SQL Server 2005 Forums

Transact-SQL (2005)

Using WHERE on calculated column data

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-02-07 : 09:28:47

The following code pulls users from a table and shows their age:


DECLARE @today datetime
SET @today = getutcdate()
SELECT
	u.[name],
	u.[surname],
	DATEDIFF(YY, u.[dob], @today) - CASE WHEN( (MONTH(u.[dob])*100 + DAY(u.[dob])) > (MONTH(@today)*100 + DAY(@today))) THEN 1 ELSE 0 END as [age]
FROM
	[tbl_Users] u

How can I expand this code to only show users whose age falls between 2 boundaries (i.e. 20 --> 30 yrs old)?
Is it possible to filter results on calculated data??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 09:32:51

Using the function found here http://www.sqlteam.com/article/datediff-function-demystified

DECLARE @today datetime
SET @today = getutcdate()

SELECT	[name],
	[surname],
	age
FROM	(
		SELECT	[name],
			[surname],
			dbo.fnYearsApart([dob], @today) AS age
		FROM	[tbl_Users]
	) AS f
where	age between 20 and 29

E 12°55'05.25"
N 56°04'39.16"

deepu_v04
Starting Member

3 Posts

Posted - 2008-02-07 : 10:24:42

Simply we can use this also

SELECT [name],
[surname],
DATEDIFF(YEAR,dob,GETDATE()) AS AGE
FROM [tbl_Users]
WHERE DATEDIFF(YEAR,dob,GETDATE()) BETWEEN 20 AND 30

Sandeep

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 10:30:42

Careful!

If a person is born Dec 31, 2007 at 23:59 PM and today is Jan 1, 2008 00:01 AM
DATEDIFF will return 1 year old (even if the two dates only are 2 minutes apart)...

As long as the day of the same month as the people born on haven't passed, DATEDIFF will always return 1 year to much.
If you read my article, you would have known this.

E 12°55'05.25"
N 56°04'39.16"

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-02-07 : 10:40:31

Thank you. Both those posts are interesting reading. Time to read your article Peso... :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources