---
title: "How to Work With DATE FORMAT: Top MySQL Examples"
description: "The content discusses various MySQL functions for date manipulation, including extraction and formatting of day, month, year, conversion of date formats, and calculations involving dates."
url: https://srinimf.com/2024/10/03/how-to-work-with-date-format-top-mysql-examples/
date: 2024-10-03
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2024/10/image-2.png?fit=1024%2C768&ssl=1
categories: ["mysql"]
tags: ["MySQL Date Examples"]
type: post
lang: en
---

# How to Work With DATE FORMAT: Top MySQL Examples

You can use various functions, to extract the date components (day, month, year) and convert or format dates in MySQL. Below are common operations involving date, month, extracting the year, and date formatting.

Related[COBOL List of Top Date Functions](https://srinimf.com/2016/01/16/how-to-get-cobol-current-date-and-integer-of-date/)

## My[SQL DATE](https://srinimf.com/2025/10/14/mastering-sql-date-extraction-and-monthly-trends-using-lag/) Format Examples

### 1. **Extract Day, Month, and Year**

#### Extract the Day:

```
SELECT 
    sales_date,
    DAY(sales_date) AS day
FROM 
    sale_month;
```

- **`DAY(sales_date)`**: Extracts the day of the month from the `sales_date`.

| sales_date | day |
| --- | --- |

| 2024-01-15 | 15 |
| --- | --- |

| 2024-02-15 | 15 |
| --- | --- |

| 2024-01-10 | 10 |
| --- | --- |

| 2024-02-20 | 20 |
| --- | --- |

| 2024-03-05 | 5 |
| --- | --- |

| 2024-04-10 | 10 |
| --- | --- |

#### Extract the Month:

```
SELECT 
    sales_date,
    MONTH(sales_date) AS month
FROM 
    sale_month;
```

- **`MONTH(sales_date)`**: Extracts the numeric value of the month (1 for January, 2 for February, etc.).

| sales_date | month |
| --- | --- |

| 2024-01-15 | 1 |
| --- | --- |

| 2024-02-15 | 2 |
| --- | --- |

| 2024-01-10 | 1 |
| --- | --- |

| 2024-02-20 | 2 |
| --- | --- |

| 2024-03-05 | 3 |
| --- | --- |

| 2024-04-10 | 4 |
| --- | --- |

#### Extract the Year:

```
SELECT 
    sales_date,
    YEAR(sales_date) AS year
FROM 
    sale_month;
```

- **`YEAR(sales_date)`**: Extracts the year from the `sales_date`.

| ales_date | year |
| --- | --- |

| 2024-01-15 | 2024 |
| --- | --- |

| 2024-02-15 | 2024 |
| --- | --- |

| 2024-01-10 | 2024 |
| --- | --- |

| 2024-02-20 | 2024 |
| --- | --- |

| 2024-03-05 | 2024 |
| --- | --- |

| 2024-04-10 | 2024 |
| --- | --- |

### 2. **Format Date as Month Name and Year**

You can format dates into a more human-readable form. For instance, showing the month’s name and year.

#### Get Month Name:

```
SELECT 
    sales_date,
    DATE_FORMAT(sales_date, '%M') AS month_name
FROM 
    sale_month;
```

- **`DATE_FORMAT(sales_date, '%M')`**: Formats the date to show the full month name (e.g., “January”, “February”).

| sales_date | month_name |
| --- | --- |

| 2024-01-15 | January |
| --- | --- |

| 2024-02-15 | February |
| --- | --- |

| 2024-01-10 | January |
| --- | --- |

| 2024-02-20 | February |
| --- | --- |

| 2024-03-05 | March |
| --- | --- |

| 2024-04-10 | April |
| --- | --- |

#### Get Month Name and Year:

```
SELECT 
    sales_date,
    DATE_FORMAT(sales_date, '%M %Y') AS month_year
FROM 
    sale_month;
```

- **`DATE_FORMAT(sales_date, '%M %Y')`**: Format the date to show the full month name followed by the year (e.g., “January 2024”).

| sales_date | month_year |
| --- | --- |

| 2024-01-15 | January 2024 |
| --- | --- |

| 2024-02-15 | February 2024 |
| --- | --- |

| 2024-01-10 | January 2024 |
| --- | --- |

| 2024-02-20 | February 2024 |
| --- | --- |

| 2024-03-05 | March 2024 |
| --- | --- |

| 2024-04-10 | April 2024 |
| --- | --- |

### 3. **Convert Date Format (YYYY-MM-DD to Other Formats)**

You can convert a date into different string formats using `DATE_FORMAT()`.

#### Convert Date to `MM-DD-YYYY`:

```
SELECT 
    sales_date,
    DATE_FORMAT(sales_date, '%m-%d-%Y') AS formatted_date
FROM 
    sale_month;
```

- **`DATE_FORMAT(sales_date, '%m-%d-%Y')`**: Converts the date to the `MM-DD-YYYY` format.

| sales_date | formatted_date |
| --- | --- |

| 2024-01-15 | 01-15-2024 |
| --- | --- |

| 2024-02-15 | 02-15-2024 |
| --- | --- |

| 2024-01-10 | 01-10-2024 |
| --- | --- |

| 2024-02-20 | 02-20-2024 |
| --- | --- |

| 2024-03-05 | 03-05-2024 |
| --- | --- |

| 2024-04-10 | 04-10-2024 |
| --- | --- |

#### Convert Date to `DD/MM/YYYY`:

```
SELECT 
    sales_date,
    DATE_FORMAT(sales_date, '%d/%m/%Y') AS formatted_date
FROM 
    sale_month;
```

- **`DATE_FORMAT(sales_date, '%d/%m/%Y')`**: Converts the date to the `DD/MM/YYYY` format.

| sales_date | formatted_date |
| --- | --- |

| 2024-01-15 | 15/01/2024 |
| --- | --- |

| 2024-02-15 | 15/02/2024 |
| --- | --- |

| 2024-01-10 | 10/01/2024 |
| --- | --- |

| 2024-02-20 | 20/02/2024 |
| --- | --- |

| 2024-03-05 | 05/03/2024 |
| --- | --- |

| 2024-04-10 | 10/04/2024 |
| --- | --- |

### 4. **Get Quarter and Week Number**

#### Get Quarter of the Year:

```
SELECT 
    sales_date,
    QUARTER(sales_date) AS quarter
FROM 
    sale_month;
```

- **`QUARTER(sales_date)`**: Returns the quarter of the year (1 for January to March, 2 for April to June, etc.).

| sales_date | quarter |
| --- | --- |

| 2024-01-15 | 1 |
| --- | --- |

| 2024-02-15 | 1 |
| --- | --- |

| 2024-01-10 | 1 |
| --- | --- |

| 2024-02-20 | 1 |
| --- | --- |

| 2024-03-05 | 1 |
| --- | --- |

| 2024-04-10 | 2 |
| --- | --- |

#### Get Week Number:

```
SELECT 
    sales_date,
    WEEK(sales_date) AS week_number
FROM 
    sale_month;
```

- **`WEEK(sales_date)`**: Returns the week number of the year for the given date.

| sales_date | week_number |
| --- | --- |

| 2024-01-15 | 3 |
| --- | --- |

| 2024-02-15 | 7 |
| --- | --- |

| 2024-01-10 | 2 |
| --- | --- |

| 2024-02-20 | 8 |
| --- | --- |

| 2024-03-05 | 10 |
| --- | --- |

| 2024-04-10 | 15 |
| --- | --- |

### 5. **Add/Subtract Days, Months, and Years**

#### Add 10 Days:

```
SELECT 
    sales_date,
    DATE_ADD(sales_date, INTERVAL 10 DAY) AS new_date
FROM 
    sale_month;
```

- **`DATE_ADD(sales_date, INTERVAL 10 DAY)`**: Adds 10 days to the `sales_date`.

| sales_date | new_date |
| --- | --- |

| 2024-01-15 | 2024-01-25 |
| --- | --- |

| 2024-02-15 | 2024-02-25 |
| --- | --- |

| 2024-01-10 | 2024-01-20 |
| --- | --- |

| 2024-02-20 | 2024-03-01 |
| --- | --- |

| 2024-03-05 | 2024-03-15 |
| --- | --- |

| 2024-04-10 | 2024-04-20 |
| --- | --- |

#### Subtract 1 Month:

```
SELECT 
    sales_date,
    DATE_SUB(sales_date, INTERVAL 1 MONTH) AS new_date
FROM 
    sale_month;
```

- **`DATE_SUB(sales_date, INTERVAL 1 MONTH)`**: Subtracts 1 month from the `sales_date`.

| sales_date | new_date |
| --- | --- |

| 2024-01-15 | 2023-12-15 |
| --- | --- |

| 2024-02-15 | 2024-01-15 |
| --- | --- |

| 2024-01-10 | 2023-12-10 |
| --- | --- |

| 2024-02-20 | 2024-01-20 |
| --- | --- |

| 2024-03-05 | 2024-02-05 |
| --- | --- |

| 2024-04-10 | 2024-03-10 |
| --- | --- |

### 6. **Truncate Date to Month or Year**

#### Truncate to the First Day of the Month:

```
SELECT 
    sales_date,
    DATE_FORMAT(sales_date, '%Y-%m-01') AS first_day_of_month
FROM 
    sale_month;
```

- **`DATE_FORMAT(sales_date, '%Y-%m-01')`**: Truncates the date to the first day of the month.

| sales_date | first_day_of_month |
| --- | --- |

| 2024-01-15 | 2024-01-01 |
| --- | --- |

| 2024-02-15 | 2024-02-01 |
| --- | --- |

| 2024-01-10 | 2024-01-01 |
| --- | --- |

| 2024-02-20 | 2024-02-01 |
| --- | --- |

| 2024-03-05 | 2024-03-01 |
| --- | --- |

| 2024-04-10 | 2024-04-01 |
| --- | --- |

#### Truncate to the First Day of the Year:

```
SELECT 
    sales_date,
    DATE_FORMAT(sales_date, '%Y-01-01') AS first_day_of_year
FROM 
    sale_month;
```

- **`DATE_FORMAT(sales_date, '%Y-01-01')`**: Truncates the date to the first day of the year.

| sales_date | first_day_of_year |
| --- | --- |

| 2024-01-15 | 2024-01-01 |
| --- | --- |

| 2024-02-15 | 2024-01-01 |
| --- | --- |

| 2024-01-10 | 2024-01-01 |
| --- | --- |

| 2024-02-20 | 2024-01-01 |
| --- | --- |

| 2024-03-05 | 2024-01-01 |
| --- | --- |

| 2024-04-10 | 2024-01-01 |
| --- | --- |

### 7. **Check if a Date Falls in a Specific Month or Year**

#### Check if the Date is in January:

```
SELECT 
    sales_date,
    IF(MONTH(sales_date) = 1, 'Yes', 'No') AS is_january
FROM 
    sale_month;
```

- **`IF(MONTH(sales_date) = 1, 'Yes', 'No')`**: Checks if the date falls in January.

| sales_date | is_january |
| --- | --- |

| 2024-01-15 | Yes |
| --- | --- |

| 2024-02-15 | No |
| --- | --- |

| 2024-01-10 | Yes |
| --- | --- |

| 2024-02-20 | No |
| --- | --- |

| 2024-03-05 | No |
| --- | --- |

| 2024-04-10 | No |
| --- | --- |

#### Check if the Year is 2024:

```
SELECT 
    sales_date,
    IF(YEAR(sales_date) = 2024, 'Yes', 'No') AS is_2024
FROM 
    sale_month;
```

- **`IF(YEAR(sales_date) = 2024, 'Yes', 'No')`**: Checks if the year is 2024.

| sales_date | is_2024 |
| --- | --- |

| 2024-01-15 | Yes |
| --- | --- |

| 2024-02-15 | Yes |
| --- | --- |

| 2024-01-10 | Yes |
| --- | --- |

| 2024-02-20 | Yes |
| --- | --- |

| 2024-03-05 | Yes |
| --- | --- |

| 2024-04-10 | Yes |
| --- | --- |

These are some of the ***Date ***manipulation and formatting techniques in ***MySQL***.

PySpark Examples

```
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, dayofmonth, month, year, date_format, quarter, weekofyear, expr

# Initialize a Spark session
spark = SparkSession.builder.appName("DateManipulation").getOrCreate()

# Sample Data (as it would be in your MySQL table)
data = [
    (1, '2024-01-15', 100.00),
    (1, '2024-02-15', 150.00),
    (2, '2024-01-10', 200.00),
    (2, '2024-02-20', 250.00),
    (3, '2024-03-05', 300.00),
    (3, '2024-04-10', 350.00)
]

# Create DataFrame
columns = ['product_id', 'sales_date', 'sales']
df = spark.createDataFrame(data, columns)

# Cast sales_date to date type
df = df.withColumn('sales_date', col('sales_date').cast('date'))

# Extract Day, Month, and Year
df = df.withColumn("day", dayofmonth(col("sales_date"))) \
       .withColumn("month", month(col("sales_date"))) \
       .withColumn("year", year(col("sales_date")))

# Format Date as Month Name and Year
df = df.withColumn("month_name", date_format(col("sales_date"), "MMMM")) \
       .withColumn("month_year", date_format(col("sales_date"), "MMMM yyyy"))

# Convert Date Format (YYYY-MM-DD to Other Formats)
df = df.withColumn("formatted_date_mm_dd_yyyy", date_format(col("sales_date"), "MM-dd-yyyy")) \
       .withColumn("formatted_date_dd_mm_yyyy", date_format(col("sales_date"), "dd/MM/yyyy"))

# Get Quarter and Week Number
df = df.withColumn("quarter", quarter(col("sales_date"))) \
       .withColumn("week_number", weekofyear(col("sales_date")))

# Add 10 Days and Subtract 1 Month
df = df.withColumn("add_10_days", expr("date_add(sales_date, 10)")) \
       .withColumn("subtract_1_month", expr("add_months(sales_date, -1)"))

# Truncate Date to Month and Year
df = df.withColumn("first_day_of_month", date_format(col("sales_date"), "yyyy-MM-01")) \
       .withColumn("first_day_of_year", date_format(col("sales_date"), "yyyy-01-01"))

# Check if Date is in January and Year is 2024
df = df.withColumn("is_january", expr("CASE WHEN month(sales_date) = 1 THEN 'Yes' ELSE 'No' END")) \
       .withColumn("is_2024", expr("CASE WHEN year(sales_date) = 2024 THEN 'Yes' ELSE 'No' END"))

# Show the results
df.select("product_id", "sales_date", "sales", "day", "month", "year", 
          "month_name", "month_year", "formatted_date_mm_dd_yyyy", "formatted_date_dd_mm_yyyy",
          "quarter", "week_number", "add_10_days", "subtract_1_month",
          "first_day_of_month", "first_day_of_year", "is_january", "is_2024").show(truncate=False)
```

Output

```
+-----------+-----------+------+---+-----+----+-----------+------------+---------------------+---------------------+-------+-----------+-----------+---------------+-------------------+----------------+----------+-------+
| product_id| sales_date| sales|day|month|year| month_name| month_year |formatted_date_mm_dd_yyyy|formatted_date_dd_mm_yyyy|quarter|week_number|add_10_days|subtract_1_month|first_day_of_month|first_day_of_year|is_january|is_2024|
+-----------+-----------+------+---+-----+----+-----------+------------+---------------------+---------------------+-------+-----------+-----------+---------------+-------------------+----------------+----------+-------+
| 1         | 2024-01-15| 100.0| 15| 1   |2024| January   | January 2024|01-15-2024          |15/01/2024          |1      |3          |2024-01-25 |2023-12-15     |2024-01-01         |2024-01-01       |Yes       |Yes    |
| 1         | 2024-02-15| 150.0| 15| 2   |2024| February  | February 2024|02-15-2024          |15/02/2024          |1      |7          |2024-02-25 |2024-01-15     |2024-02-01         |2024-01-01       |No        |Yes    |
| 2         | 2024-01-10| 200.0| 10| 1   |2024| January   | January 2024|01-10-2024          |10/01/2024          |1      |2          |2024-01-20 |2023-12-10     |2024-01-01         |2024-01-01       |Yes       |Yes    |
| 2         | 2024-02-20| 250.0| 20| 2   |2024| February  | February 2024|02-20-2024          |20/02/2024          |1      |8          |2024-03-01 |2024-01-20     |2024-02-01         |2024-01-01       |No        |Yes    |
| 3         | 2024-03-05| 300.0| 5 | 3   |2024| March     | March 2024  |03-05-2024          |05/03/2024          |1      |10         |2024-03-15 |2024-02-05     |2024-03-01         |2024-01-01       |No        |Yes    |
| 3         | 2024-04-10| 350.0| 10| 4   |2024| April     | April 2024  |04-10-2024          |10/04/2024          |2      |15         |2024-04-20 |2024-03-10     |2024-04-01         |2024-01-01       |No        |Yes    |
+-----------+-----------+------+---+-----+----+-----------+------------+---------------------+---------------------+-------+-----------+-----------+---------------+-------------------+----------------+----------+-------+
```

References

- [Querying MYSQL Book](https://www.amazon.in/Querying-MySQL-accessible-operations-extraction/dp/9355512678?tag=srinimf-21)
- [High-Performance MySQL](https://www.amazon.in/High-Performance-MySQL-Strategies-Operating/dp/9355420919?tag=srinimf-21)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2024%2F10%2F03%2Fhow-to-work-with-date-format-top-mysql-examples%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2024%2F10%2Fimage-2.png%3Ffit%3D1024%252C768%26ssl%3D1&description=How%20to%20Work%20With%20DATE%20FORMAT%3A%20Top%20MySQL%20Examples)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2024/10/03/how-to-work-with-date-format-top-mysql-examples/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2024/10/03/how-to-work-with-date-format-top-mysql-examples/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2024/10/03/how-to-work-with-date-format-top-mysql-examples/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2024/10/03/how-to-work-with-date-format-top-mysql-examples/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Work%20With%20DATE%20FORMAT%3A%20Top%20MySQL%20Examples&body=https%3A%2F%2Fsrinimf.com%2F2024%2F10%2F03%2Fhow-to-work-with-date-format-top-mysql-examples%2F&share=email)
-

### Like this:

Like Loading…