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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy