MySQL CURDATE() Function

The `CURDATE()` function in MySQL returns the current date in the `YYYY-MM-DD` format. It is commonly used to retrieve and compare the current date in SQL queries.

Usage

The `CURDATE()` function is used when you need to work with the current date in data operations, such as filtering records or setting default date values. It does not require any arguments and can be used in `SELECT`, `INSERT`, `UPDATE`, and `WHERE` clauses.

sql
CURDATE();

This syntax returns the current date as a date value.

Note: `CURDATE()` and `CURRENT_DATE()` are synonyms and can be used interchangeably.

Examples

1. Basic Retrieval of Current Date

sql
SELECT CURDATE();

This example simply retrieves the current date from the MySQL server's system clock.

2. Using CURDATE() in a WHERE Clause

sql
SELECT order_id, order_date
FROM orders
WHERE order_date = CURDATE();

In this example, `CURDATE()` is used to filter orders that were placed on the current date.

3. Inserting Current Date into a Table

sql
INSERT INTO attendance (employee_id, date_checked_in)
VALUES (123, CURDATE());

Here, `CURDATE()` is used to automatically insert the current date into the `date_checked_in` column when a new record is added.

4. Complex Query with Date Arithmetic

sql
SELECT employee_id
FROM attendance
WHERE date_checked_in = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

This example retrieves records of employees who checked in the day before the current date.

Tips and Best Practices

• Ensure time zone consistency. Make sure your application and MySQL server are using the same time zone settings to avoid unexpected results.
• Use `CURDATE()` for date-only values. If you need both date and time, consider using `NOW()` instead.
• Combine with date functions. Use `CURDATE()` alongside other date functions like `DATE_ADD()` for more complex date calculations.
• Be aware of implicit casting. When comparing `CURDATE()` with datetime columns, implicit casting may occur, which could affect performance. To optimize, explicitly cast the datetime column to a date using `CAST(datetime_column AS DATE)`.