Jstngoulet Jstngoulet - 29 days ago 7
MySQL Question

How to determine if person is over 21 in MySQL

I am trying to create a view in MySQL. I want it to display the user email, first-lastname, their DOB, and their current age. I only want to show the users that are over 21 years of age.

This is what I have:

CREATE VIEW 'valid_Users' AS
SELECT Persons.Email AS Email, Persons.FName + ' ' + Persons.LName AS Name, Persons.DOB AS Birth_Date, DATEDIFF(Persons.DOB, NOW()) AS Current_Age'
FROM Persons
WHERE DATEDIFF(Persons.DOB, NOW()) > 21;


The issue is that the Where clause returns a negative number, which is obviously not a correct age. How would I edit this code to determine the proper age of a person?

I know the names aren't working - yet... but here is an image of the some current data. The actual table is 'Users'

enter image description here

Answer

Try:

CREATE VIEW 'valid_Users' AS 
SELECT Persons.Email AS Email, Persons.FName + ' ' + Persons.LName AS Name, Persons.DOB AS Birth_Date, DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), Persons.DOB)), "%Y")+0 AS Current_Age'
FROM Persons
WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), Persons.DOB)), "%Y")+0 > 21;

OR:

CREATE VIEW 'valid_Users' AS 
SELECT Persons.Email AS Email, Persons.FName + ' ' + Persons.LName AS Name, Persons.DOB AS Birth_Date, TIMESTAMPDIFF(YEAR, Persons.DOB, NOW()) AS Current_Age'
FROM Persons
WHERE TIMESTAMPDIFF(YEAR, Persons.DOB, NOW()) > 21;