Search code examples
mysqldatefielddatedifftimestampdiff

Getting NULL values after using timestampdiff and curdate, MySQL

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?

Solution

  • 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.