CB Moate CB Moate - 6 months ago 11
MySQL Question

Calculating age while inserting into a new table

I have a table of salespeople and I'm trying to create a second table from that data to establish the senior sales team. In the first table we have their birthdates stored however I want to just store their current age in the new table.

This is my salesperson schema

CREATE TABLE Salesperson
(
SalespersonId BIGINT,
Name VARCHAR(50),
BirthDate DATE,
Salary INT
);


All I'm trying to do is add their name and age to the new table

INSERT INTO SeniorSales (Name, Age)
SELECT Name, FLOOR(DATEDIFF(DAY, @BirthDate, @TargetDate) / 365.25)
FROM Salesperson
WHERE Salary > 100,000;


I'm getting an error that says I have "Incorrect parameter count in the call to native function 'DATEDIFF'" I will admit that I'm relatively new to mySQL and I took the age calculation function off a google search.

Answer

DATEDIFF sintax use two date only this way DATEDIFF(date1,date2)

in your case you should use

 INSERT INTO SeniorSales (Name, Age)
 SELECT Name, FLOOR(DATEDIFF(@TargetDate, @BirthDate) / 365.25) 
 FROM Salesperson 
 WHERE Salary > 100,000;