DATE_FORMAT and EXTRACT in SQL

Introduction

We use DATE_FORMAT and EXTRACT to get parts of dates or show dates in a way that is easy to understand.

When you want to show a date as 'day-month-year' instead of the default format.

When you need to find the year or month from a date to group or filter data.

When you want to display the time part of a date in a special way.

When you want to calculate how many days or months have passed since a date.

When you want to sort or compare dates by parts like year or day.

Syntax

SQL

DATE_FORMAT(date, format)
EXTRACT(part FROM date)

DATE_FORMAT changes how a date looks using format codes like %Y for year, %m for month, %d for day.

EXTRACT gets one part of a date like year, month, day, hour, minute, or second.

Examples

Shows the date as day/month/year like 15/06/2024.

SQL

SELECT DATE_FORMAT('2024-06-15', '%d/%m/%Y');

Gets the year part from the date, which is 2024.

SQL

SELECT EXTRACT(YEAR FROM DATE('2024-06-15'));

Shows the current time as hours:minutes:seconds.

SQL

SELECT DATE_FORMAT(NOW(), '%H:%i:%s');

Gets the month part from the date, which is 6.

SQL

SELECT EXTRACT(MONTH FROM DATE('2024-06-15'));

Sample Program

This query shows the date in a friendly format and also extracts the year, month, and day parts separately.

SQL

SELECT
  DATE_FORMAT('2024-12-25', '%W, %M %d, %Y') AS formatted_date,
  EXTRACT(YEAR FROM DATE('2024-12-25')) AS year_part,
  EXTRACT(MONTH FROM DATE('2024-12-25')) AS month_part,
  EXTRACT(DAY FROM DATE('2024-12-25')) AS day_part;

Important Notes

DATE_FORMAT is very useful for showing dates in reports or user interfaces.

EXTRACT helps when you want to filter or group data by parts of a date.

Remember that format codes in DATE_FORMAT are case sensitive and must be used correctly.

Summary

DATE_FORMAT changes how a date looks using special codes.

EXTRACT gets one part of a date like year, month, or day.

Both help you work with dates in ways that fit your needs.