phpmyadmin Question posted in
The official documentation can be found here.

How can I use date arithmetic in MySQL when one of the two dates stores only the year but not month or day? – Phpmyadmin

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

We can see that some functions do not like incomplete dates:

Some date functions can be used with “zero” dates or incomplete dates such as ‘2001-11-00’, whereas others cannot.

I want to subtract two dates like this:

SELECT DATEDIFF(CURRENT_DATE(), birthdate)

birthdate is a DATE column according to the phpMyAdmin console.

But some of the birth dates don’t have the month or day, example '1874-00-00'. You cannot get the days difference because the days are not known.

Is there a way to tell it to assume Jan 1 or something? Or would I need to resort to some building the date up myself so it recognizes it?

I really just need the approximate number of years difference. But I get NULL.

Thanks.

Tags:

2

Answers


  1. 0

    Use only YEAR from your field & do simple subtraction:

    SELECT YEAR(NOW())-YEAR(birthdate) FROM YOUR_TABLE_NAME;

    It will work both for valid & invalid date

    Login or Signup to reply.
  2. 0

    If you have dates like '1874-00-00' then the data type of the column is VARCHAR unless you have disabled NO_ZERO_IN_DATE mode which is enabled by default.

    I really just need the approximate number of years difference

    If the year is always included in your dates and it is 4 digits at the start then all you need is to subtract your date form the current year:

    SELECT birthdate,
       YEAR(NOW()) - birthdate AS age
FROM tablename

    MySql will do implicit conversion of the birthdate to an integer which will be just the year.

    For this sample data:

    CREATE TABLE tablename(birthdate VARCHAR(20));
                                         
INSERT INTO tablename(birthdate) VALUES
('1950-05-15'), ('1960-08-00'), ('1970-00-00');

    the result will be:

    | birthdate  | age |
| ---------- | --- |
| 1950-05-15 | 70  |
| 1960-08-00 | 60  |
| 1970-00-00 | 50  |

    Or if you want to be more precise when the birthdates are valid dates:

    SELECT birthdate,
       COALESCE(
         TIMESTAMPDIFF(YEAR, birthdate, NOW()),
         YEAR(NOW()) - birthdate
       ) age  
FROM tablename

    See the demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search