February 25, 2020 at 5:01 pm
Hi everyone! (My first post, so I hope it's the right board) So I got a bit of code for what I'm trying to do. I have a column with birthdates and I want to get the age out of that through select. From that selection I only want the age of the people where the age number can be divided by 4. I have no clue how I can use mod on this code?
So basically I would like to know how you can make calculations on columns you've created within your selection (if that makes sense)
select birthdate
getdate() as [today],
datediff (YY, birthdate , Getdate()) -
case
when
dateadd(YY,datediff(yy,birthdate,Getdate()), birthdate) > getdate ()
then 1
else 0
end as [age]
February 25, 2020 at 6:29 pm
WHERE datediff (YY, birthdate , Getdate()) % 4 = 0 ?
February 25, 2020 at 6:39 pm
You can use a CTE or CROSS APPLY:
CTE Version:
WITH ageDetail
AS (
SELECT Birthdate
, CurrentDate = getdate()
, Age = datediff(yy, Birthdate, getdate())
- case when dateadd(yy, datediff(yy, Birthdate, getdate()), Birthdate) > getdate()
then 1
else 0
end
FROM yourTable
)
SELECT *
FROM ageDetail
WHERE Age % 4 = 0;
CROSS APPLY:
SELECT Birthdate
, CurrentDate = getdate()
, Age
FROM yourTable
CROSS APPLY (VALUES (datediff(yy, Birthdate, getdate()) - case when dateadd(yy, datediff(yy, Birthdate, getdate()), Birthdate) > getdate() then 1 else 0 end)) AS e1(Age)
WHERE Age % 4 = 0;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 26, 2020 at 3:48 pm
Wow! Both CTE and Cross Apply seem to work indeed! Thanks a lot!
February 26, 2020 at 6:58 pm
You are welcome
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply