MySQL Formula to Convert Days to Years & Months

When working with date and time values in MySQL, it’s common to store durations in days. However, sometimes you need to present those days as a more human-readable format—like years and months. For example, showing “2 years, 3 months” instead of “825 days.”

Here’s a simple way to achieve this using a MySQL formula.

---

Converting Days to Years and Months

Since MySQL doesn’t have a built-in function to directly convert days into years and months, we can use arithmetic with division (/) and modulo (%) operators.

SELECT

FLOOR(days / 365) AS years,

FLOOR((days % 365) / 30) AS months

FROM

your_table;

---

How It Works

• FLOOR(days / 365)
  Divides the total days by 365 to get whole years.
• (days % 365)
  Gets the leftover days after subtracting the full years.
• FLOOR((days % 365) / 30)
  Divides the leftover days by 30 to approximate months.

---

Example

Suppose your table looks like this:

CREATE TABLE durations (days INT);

INSERT INTO durations (days) VALUES (400), (825), (1200);

Running the query:

SELECT

days,

FLOOR(days / 365) AS years,

FLOOR((days % 365) / 30) AS months

FROM durations;

Would produce:

days	years	months
400	1	1
825	2	3
1200	3	3

---

Notes and Limitations

• This method assumes 365 days per year and 30 days per month. It’s an approximation, not a precise calendar conversion.
• If you need exact calendar-aware calculations (e.g., accounting for leap years or exact month lengths), you’ll need to work with MySQL date functions and actual date values instead of raw day counts.

---

Final Thoughts

Converting days to years and months in MySQL can be done easily with simple math functions. While the above approach works well for approximations, consider whether you need exact date-based calculations before choosing the method.

If you often store durations in days, this formula provides a quick way to present data in a more user-friendly format.