Reputation: 23
I am new to SQL and am trying to write a query that returns those contacts who have turned 18 based on the current date (today). I thought about the query below but I am unsure how to add the part where 'and date equals today'.
I appreciate that I am probably making it more complicated than it needs to be.
SELECT date_of_birth
FROM contacts
WHERE ((DATEDIFF(Year,date_of_birth,GETDATE())>'17') AND
(DATEDIFF(Year,date_of_birth,GETDATE())<'19'))
date_of_birth is greater than 17 but less than 19
Upvotes: 0
Views: 7323
Reputation: 303
You can try this simple query instead of using two conditions:
Select date_of_birth from contacts
where datediff(yy,date_of_birth,getdate()) = 18
Upvotes: 0
Reputation:
If your date_of_birth
field is stored in date only format you can just add 18 Years to it and compare to today's date.
DATEADD(YEAR,18,date_of_birth) = CONVERT(DATE,GETDATE())
Because GETDATE()
gets DATETIME
results you can just convert it to DATE
Here is sample code to test the logic.
DECLARE @date_of_birth DATE = '03/18/1996'
SELECT CASE WHEN DATEADD(YEAR, 18, @date_of_birth) = CONVERT(DATE, GETDATE()) THEN 'TRUE'
ELSE 'FALSE'
END
If you want to get all people who are 18. Means they already turned 18 but have not turned 19 yet, you can use DATEDIFF
, in the following test, in first select statement both columns will return 'TRUE'
but after I change DOB to '3/17/1996'
only second case in second select returns 'TRUE'
DECLARE @date_of_birth DATE = '03/18/1996'
SELECT CASE WHEN DATEADD(YEAR, 18, @date_of_birth) = CONVERT(DATE, GETDATE()) THEN 'TRUE'
ELSE 'FALSE'
END
,CASE WHEN DATEDIFF(YEAR, @date_of_birth, GETDATE()) = 18 THEN 'TRUE'
ELSE 'FALSE'
END
SET @date_of_birth = '03/17/1996'
SELECT CASE WHEN DATEADD(YEAR, 18, @date_of_birth) = CONVERT(DATE, GETDATE()) THEN 'TRUE'
ELSE 'FALSE'
END
,CASE WHEN DATEDIFF(YEAR, @date_of_birth, GETDATE()) = 18 THEN 'Today18'
ELSE 'FALSE'
END
Upvotes: 0
Reputation: 375
Just considering the Year wont fetch you the correct result, you need to consider Day and Month too. Check this out, we have been using this in our application for sometime:
SELECT date_of_birth
FROM contacts
WHERE (FLOOR(DATEDIFF(DAY, date_of_birth,GETDATE()) / 365.25) between 17 and 18
Upvotes: 1
Reputation: 839
Try using DATEADD instead. You can use negative numbers to subtract.
http://technet.microsoft.com/en-us/library/ms186819.aspx
Upvotes: 0