Getting NULL values after using timestampdiff and curdate, MySQL-CodePudding

I have table called Client, which has the fields ClientFirstName, ClientLastName, Occupation and ClientDob. I want to get the age of the clients. I will be using the ClientDob field which has a date YEAR datatype.

I have done research and tried querying with this:

select ClientFirstName, ClientLastName, ClientDob, curdate(),
    timestampdiff(YEAR, ClientDob, curdate()) as Age
from Client;

In my results, I get all fields, but the Age field has null values, what is the reason for this? Query was performed successfully but with null values, no errors shown in the response of the action. What should I check in order to try it again? The ClientDob has the YEAR datatype, and curdate shows year, month and day. Is this the reason why?

CodePudding user response：

A YEAR doesn't have the day of year in it, so it's not possible to calculate the difference with a date.

You should extract the YEAR from curdate() and subtract that.

select ClientFirstName, ClientLastName, ClientDob, curdate(),
    YEAR(curdate()) - ClientDob AS Age
from Client;

Note that storing ClientDob as just a YEAR means you won't calculate the correct age depending on whether the current date is before or after their birthday. If they're born on 2021-12-31 and the current date is 2022-01-01, it will say their age is 1 even though they're just 1 day old.

CodePudding user response：

The reason you are getting a null value is probably due to the fact that the ClientDob field is not properly formatted. The timestampdiff() function requires both the start and end dates to be in the same format. If the ClientDob field is not in a valid date format, the timestampdiff() function will return a null value. To ensure that the ClientDob field is properly formatted, use the DATE_FORMAT() function:

SELECT ClientFirstName, ClientLastName, ClientDob, curdate(), 
    timestampdiff(YEAR, DATE_FORMAT(ClientDob, '%Y-%m-%d'), curdate()) as Age
FROM Client;