MariaDB DATE Functions in Action: Examples and Use Cases

1. DATE()
   This function extracts the date portion from a datetime or date expression. It returns the date in the format "YYYY-MM-DD".

   • Example:

     SELECT DATE('2024-06-20 10:30:00');
     

     • Output:

       2024-06-20
       

2. CURDATE() or CURRENT_DATE()
   These functions are synonymous and return the current date as stored on the database server.

   • Example:

     SELECT CURDATE();
     

     • Output: (assuming today's date is June 20th, 2024)

       2024-06-20
       

3. DATE_FORMAT()
   This function allows you to format a date value according to a specified mask. It takes two arguments: the date expression and the format mask.

   • Example:

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

     • Output:

       20/06/2024
       

For additional functionalities, you can explore functions like:

• DATE_SUB() or SUBDATE()
  Subtract a specified time interval from a date value.
• DATE_ADD() or ADDDATE()
  Add a specified time interval to a date value.

---

Extracting Date from DATETIME

-- Suppose you have a table "sales" with a "sale_datetime" column
SELECT product_name, DATE(sale_datetime) AS sale_date
FROM sales;

This query retrieves the "product_name" and extracts the date portion from the "sale_datetime" column (assuming it's a DATETIME datatype) and renames it to "sale_date".

Finding Orders within the Last Week

-- Assuming an "orders" table with an "order_date" column
SELECT order_id, customer_name
FROM orders
WHERE CURDATE() - INTERVAL 7 DAY <= order_date;

This query finds orders placed within the last week. It subtracts 7 days from the current date (using CURDATE()) and compares it to the "order_date".

Calculating Age based on Birthdate

-- Assuming a "customers" table with a "birthdate" column
SELECT customer_name, YEAR(CURDATE()) - YEAR(birthdate) AS age
FROM customers;

This query calculates the age of each customer. It subtracts the year of birth (extracted using YEAR()) from the current year (YEAR(CURDATE())).

Formatting Dates for Reports

-- Assuming a "transactions" table with a "transaction_date" column
SELECT CONCAT('Transaction on: ', DATE_FORMAT(transaction_date, '%d-%b-%Y')) AS transaction_details
FROM transactions;

This query creates a new column "transaction_details" that displays a formatted message including the transaction date. It uses CONCAT to combine text with the formatted date using DATE_FORMAT() with a custom mask ("%d-%b-%Y").

---

Extracting the Date Portion

• CURDATE() or CURRENT_DATE()
  If you simply want the current date, these functions are a good alternative to extracting the date from a DATETIME using DATE().

Working with Different Date Formats

• STR_TO_DATE()
  This function is an alternative if you need to convert a string representation of a date into a usable date value. It's useful when working with user input or dates stored in non-standard formats.

Performing Date Calculations

• DATE_SUB() or SUBDATE()
  Similarly, these functions are alternatives for subtracting a time interval from a date, offering a more concise way compared to manual calculations.

• DATE_ADD() or ADDDATE()
  These functions are alternatives for adding a specified time interval to a date value. They provide more flexibility than extracting the date and then adding days/months/years manually.

Formatting Dates

• DATE_FORMAT()
  This function remains the best option for formatting a date value according to a desired mask. It provides a high level of control over the output format.