Alternatives to CURDATE: When to Use Different Functions

CURDATE Function in MariaDB

The CURDATE function is a built-in function in MariaDB that returns the current date as a string in the format YYYY-MM-DD. It's a convenient way to incorporate the current date into your SQL queries without needing to manually specify it.

Syntax

CURDATE()

The parentheses are optional but can be included for clarity.

Behavior

• The returned value is always in the YYYY-MM-DD format, regardless of the session settings or database character set.
• It retrieves the system date from the MariaDB server.
• The CURDATE function is evaluated at the time the SQL statement is executed.

Example

SELECT CURDATE() AS today_date;

This query will return the current date as a string, for example:

+------------+
| today_date |
+------------+
| 2024-06-13 |
+------------+

Use Cases

• Inserting the current date into a table column:

INSERT INTO orders (order_date) VALUES (CURDATE());

• Filtering data based on the current date:

SELECT * FROM tasks WHERE due_date >= CURDATE();

Key Points

• For time-related information, consider using other date and time functions like CURRENT_TIME, CURRENT_TIMESTAMP, or NOW().
• It's not affected by time zones or session settings.
• CURDATE is a deterministic function, meaning it always returns the same value for a given execution time.

• While CURDATE is a convenient way to access the current date, it's important to consider whether storing the current date directly in a database table is the best approach for your specific application. In some cases, it might be more appropriate to calculate the date when needed within your application logic.

---

Filtering Orders Placed Today

This query retrieves orders placed on the current date from an orders table:

SELECT * FROM orders
WHERE order_date = CURDATE();

Calculating Age Based on Birthdate

This query calculates the age of a person based on their birthdate stored in the birthdate column:

SELECT name, FLOOR((CURDATE() - birthdate) / 365.25) AS age
FROM users;

Inserting Current Date with Default Value

This code snippet creates a table with a created_at column that automatically gets the current date when a new row is inserted:

CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  created_at DATE NOT NULL DEFAULT CURDATE()
);

Updating a Column with Current Date

This query updates the last_updated column in a products table to the current date:

UPDATE products
SET last_updated = CURDATE()
WHERE product_id = 123;

Inserting Current Date with Prepared Statement

This code demonstrates using a prepared statement to insert a record with the current date:

-- Prepare the statement
SET @date = CURDATE();
PREPARE stmt INSERT INTO logs (message, logged_at) VALUES (?, @date);

-- Execute the statement with a specific message
SET @message = 'This is a log message.';
EXECUTE stmt USING @message;

-- Deallocate the statement
DEALLOCATE stmt;

---

1. • Behavior
     Returns the current date and time as a combined value in the format YYYY-MM-DD HH:MM:SS[.fractional seconds].
   • Use Case
     More suitable when you require both date and time information within your query.
   • Example
     

   SELECT NOW() AS current_datetime;
   

2. CURRENT_TIMESTAMP function
   

   • Behavior
     Similar to NOW(), returns the current date and time, but its behavior can be influenced by session settings or database time zone configuration.
   • Use Case
     Use with caution if time zone consistency is crucial. NOW() might be a safer choice.

3. GET_FORMATTED_DATE function (MariaDB 10.2+)
   

   • Behavior
     Provides more flexibility by allowing you to specify the desired output format for the current date.
   • Syntax
     GET_FORMATTED_DATE(format) where format is a string defining the date format (e.g., '%Y-%m-%d' for YYYY-MM-DD).
   • Use Case
     Ideal for situations where you need the current date in a specific non-standard format.

Function	Output
CURDATE()	Current date in YYYY-MM-DD format
NOW()	Current date and time in YYYY-MM-DD HH:MM:SS[.fractional seconds] format
CURRENT_TIMESTAMP	Current date and time (potentially influenced by time zone settings)
GET_FORMATTED_DATE	Current date in a user-specified format