Srinimf

How to Work With DATE FORMAT: Top MySQL Examples

·

Tags:
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.

MySQL DATE 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_dateday
2024-01-1515
2024-02-1515
2024-01-1010
2024-02-2020
2024-03-055
2024-04-1010

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_datemonth
2024-01-151
2024-02-152
2024-01-101
2024-02-202
2024-03-053
2024-04-104

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_dateyear
2024-01-152024
2024-02-152024
2024-01-102024
2024-02-202024
2024-03-052024
2024-04-102024

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_datemonth_name
2024-01-15January
2024-02-15February
2024-01-10January
2024-02-20February
2024-03-05March
2024-04-10April

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_datemonth_year
2024-01-15January 2024
2024-02-15February 2024
2024-01-10January 2024
2024-02-20February 2024
2024-03-05March 2024
2024-04-10April 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_dateformatted_date
2024-01-1501-15-2024
2024-02-1502-15-2024
2024-01-1001-10-2024
2024-02-2002-20-2024
2024-03-0503-05-2024
2024-04-1004-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_dateformatted_date
2024-01-1515/01/2024
2024-02-1515/02/2024
2024-01-1010/01/2024
2024-02-2020/02/2024
2024-03-0505/03/2024
2024-04-1010/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_datequarter
2024-01-151
2024-02-151
2024-01-101
2024-02-201
2024-03-051
2024-04-102

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_dateweek_number
2024-01-153
2024-02-157
2024-01-102
2024-02-208
2024-03-0510
2024-04-1015

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_datenew_date
2024-01-152024-01-25
2024-02-152024-02-25
2024-01-102024-01-20
2024-02-202024-03-01
2024-03-052024-03-15
2024-04-102024-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_datenew_date
2024-01-152023-12-15
2024-02-152024-01-15
2024-01-102023-12-10
2024-02-202024-01-20
2024-03-052024-02-05
2024-04-102024-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_datefirst_day_of_month
2024-01-152024-01-01
2024-02-152024-02-01
2024-01-102024-01-01
2024-02-202024-02-01
2024-03-052024-03-01
2024-04-102024-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_datefirst_day_of_year
2024-01-152024-01-01
2024-02-152024-01-01
2024-01-102024-01-01
2024-02-202024-01-01
2024-03-052024-01-01
2024-04-102024-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_dateis_january
2024-01-15Yes
2024-02-15No
2024-01-10Yes
2024-02-20No
2024-03-05No
2024-04-10No

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_dateis_2024
2024-01-15Yes
2024-02-15Yes
2024-01-10Yes
2024-02-20Yes
2024-03-05Yes
2024-04-10Yes

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

Srini's avatar

Srini

Data Engineer with deep AI and Generative AI expertise, crafting high-performance data pipelines in PySpark, Databricks, and SQL. Skilled in Python, AWS, and Linux—building scalable, cloud-native solutions for smart applications.

Latest Posts

Jooble.org jobs