CODEWITHSUNDEEP
sqlsql-server
MartinB
MartinB

Reputation: 23

SQL Query for those people who turned 18 from today's date

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

Answers (4)

Vaibhav
Vaibhav

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

user275683
user275683

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

Vasan
Vasan

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

ScubaManDan
ScubaManDan

Reputation: 839

Try using DATEADD instead. You can use negative numbers to subtract.

http://technet.microsoft.com/en-us/library/ms186819.aspx

Upvotes: 0

Related Questions