0

Suppose I have this query:

SELECT CURRENT_DATE-DOB as age FROM customerDetails 
WHERE customerid = (SELECT max(customerid) FROM 
customerDetails)

which in SQLTE3 converted a customers date of birth in the format e.g. 2002-08-11 (as a date field) into their current age, e.g. 17, could I repeat the same logic for an MYSQL query...

Is the syntax different...because the same query produces this when ran in MYSQL Workbench rather than DB Broswswer for SQLite3

20198120
grimReaperZ
  • 71
  • 5
  • You want this solution in mysql ? – VBoka Jan 22 '20 at 17:57
  • check this answer . `https://stackoverflow.com/questions/10506731/get-difference-in-years-between-two-dates-in-mysql-as-an-integer` – wui Jan 22 '20 at 18:00
  • This answer deals with the subject thoroughly: https://stackoverflow.com/questions/48756627/calculating-age-from-birthday-tsql-oracle-and-any-others – Steve Jan 22 '20 at 20:13

3 Answers3

0

Substracting dates in MySQL does not do what you expect. It actually turns the dates to integers, and substracts them. So typically date 2020-01-22 becomes integer 20200122. This is not what you want.

One method to compute an age is to use date function TIMESTAMPDIFF(), like so:

timestampdiff(year, dob, curdate())
GMB
  • 216,147
  • 25
  • 84
  • 135
0

In SQLServer you can write DATEDIFF(year, GETDATE(), '2002/08/11').

Nave Sade
  • 441
  • 2
  • 6
0

One more way to do it is:

select YEAR(CURDATE()) - YEAR(20000120)

some explanation:

currdate() function returns current date Year() function returns the year for a given date

As GMB commented this solution above will return 18 for your case because the person will have 17 years and 6 months. This will return years and months:

select CONCAT(YEAR(CURDATE()) - YEAR(20020811) -1 
              , '.'
              , ABS(MONTH(CURDATE()) - MONTH(20020811)+1)) as age
FROM customerDetails 
WHERE customerid = (SELECT max(customerid) 
                    FROM customerDetails)

or if you change it with column name DOB:

select CONCAT(YEAR(CURDATE()) - YEAR(DOB) -1 
              , '.'
              , ABS(MONTH(CURDATE()) - MONTH(DOB)+1)) as age
FROM customerDetails 
WHERE customerid = (SELECT max(customerid) 
                    FROM customerDetails)

concat() concatenates parameters inside of the brackets. Make sure that the column DOB is the column where the date of the birth is inserted...

Here is a DEMO

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • 2
    This does not take into account the date and month, so it behaves like everyone was born on January 1st. – GMB Jan 22 '20 at 18:02
  • Thanks @GMB, changed the solution to the one that might be of some help... Cheers! – VBoka Jan 22 '20 at 18:10
  • 1
    I will mark as correct when you show me to include the WHERE customerid = SELECT max(customerid) FROM customerDetails – grimReaperZ Jan 22 '20 at 18:11
  • @grimReaperZ, I have update my answer. Please do not forget you can now also upvote because you have more than 15 points. Please let me know if something is not working... – VBoka Jan 22 '20 at 18:14