Working with Dates in MySQL

Dates and times are central to most applications -- orders have timestamps, users have birthdays, and reports need time-based grouping. MySQL provides a comprehensive set of date functions that let you extract parts of a date, perform arithmetic, format output, and compare temporal values.

Getting the Current Date and Time

MySQL offers several functions to retrieve the current moment. NOW() returns the full datetime,CURDATE() returns just the date, and CURTIME() returns just the time.

SELECT
  NOW()      AS current_datetime,  -- 2025-07-15 14:30:00
  CURDATE()  AS current_date,      -- 2025-07-15
  CURTIME()  AS current_time;      -- 14:30:00

Extracting Date Parts

Use YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), and SECOND() to pull specific components from a datetime value. The EXTRACT() function offers an alternative syntax.

SELECT
  order_date,
  YEAR(order_date)  AS order_year,
  MONTH(order_date) AS order_month,
  DAY(order_date)   AS order_day,
  DAYNAME(order_date) AS day_of_week
FROM orders;

Date Arithmetic

DATE_ADD() and DATE_SUB() add or subtract intervals from a date. The INTERVAL keyword supports units like DAY, MONTH, YEAR, HOUR, and MINUTE.

-- Orders from the last 30 days
SELECT *
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Calculate subscription expiry
SELECT
  user_id,
  start_date,
  DATE_ADD(start_date, INTERVAL 1 YEAR) AS expiry_date
FROM subscriptions;

Calculating Differences

DATEDIFF() returns the number of days between two dates. TIMESTAMPDIFF() is more flexible and supports different units.

SELECT
  first_name,
  hire_date,
  DATEDIFF(CURDATE(), hire_date) AS days_employed,
  TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_employed
FROM employees;

Formatting Dates

DATE_FORMAT() converts a date into a custom string representation using format specifiers like%Y (4-digit year), %m (month), %d (day), and %H:%i:%s(time).

SELECT
  DATE_FORMAT(order_date, '%M %d, %Y') AS formatted_date,
  DATE_FORMAT(order_date, '%Y-%m')     AS year_month
FROM orders;
-- 'July 15, 2025', '2025-07'

Parsing Strings to Dates

Use STR_TO_DATE() to convert a string into a date when the format does not match MySQL's defaultYYYY-MM-DD. This is especially useful when importing data from CSV files or external systems.

SELECT STR_TO_DATE('15/07/2025', '%d/%m/%Y') AS parsed_date;
-- Result: 2025-07-15