1

I have 2 dates to compare so I can count years between both date. If days is less than 365 days then I use interval to make it as 1 year. However I have problem if both date is similar in day for example 2019-10-15 to 2020-10-15 will be displayed as 2 years when I want it to be displayed as 1 year. Here is my code with not the result I want:

SELECT
  TIMESTAMPDIFF(
    YEAR,
    '2019-10-15',
    '2020-10-15' + INTERVAL 1 YEAR
  ) AS `year`
FROM
  dob

Result:

enter image description here

What I want is:

2019-10-15 to 2020-10-14 = 1 year

2019-10-15 to 2020-10-15 = 1 year

2019-10-15 to 2020-10-16 = 2 year

How can I achieved that? Thanks in advance.

sg552
  • 1,521
  • 6
  • 32
  • 59

1 Answers1

3

Does this do what you want? It seems you're only one day out from the expected output so I just subtracted a day.

SELECT 
TIMESTAMPDIFF
( YEAR, '2019-10-15',
 '2020-10-15' 
+ INTERVAL 1 YEAR
- INTERVAL 1 DAY ) 
AS `year` 
FROM dob
Simon Notley
  • 2,070
  • 3
  • 12
  • 18
  • yes it is. I didn't know you could add another `interval` in there. Thank you very much. – sg552 Dec 01 '19 at 19:01