MySQL current date

Syntax of MySQL CURDATE() Function

There is a one-liner syntax to fetch the MySQL current date.

The function comprises empty parentheses as no argument is needed by this function to retrieve the current date. It will return the current date from the system clock of the MySQL server.

Parameters of MySQL CURDATE() Function

The MySQL CURDATE() function returns the current date from the server itself. There is no need to consider any parameters for the same, so arguments are not passed to this function.

Return Value of MySQL CURDATE() Function

The return value of the MySQL current date or CURDATE() function can be either YYYY-MM-DD or YYYYMMDD and the result will consist of a single column. These return values vary by the value used with the function while calling it. 

As stated above, the MySQL CURDATE() function has empty parentheses and no parameters, so its return value varies when this function is being called as follows:

• When only the function is called without specifying any numerical or string value with it, it will simply return a string in the format YYYY-MM-DD and this is its default behavior. 
• When we add/subtract a numeric value to/from the function, the result will eventually be a string in the format YYYYMMDD.

Exceptions of MySQL CURDATE() Function

The MySQL current date has no exceptions to consider as it is a simple function. It's important to know the fact that it returns the value based on the timezone of the MySQL server.

However, the CURDATE() function might give you an incorrect output when you're trying to add/subtract a numeric value (that exceeds the current month's limit) to/from it. The correct alternative is to use DATE_ADD() or DATE_SUB() to perform any arithmetic operations on a date.

If we are also concerned about the format of the value returned, the DATE_FORMAT can also be used along with this function.

How does the MySQL CURDATE() Function Work?

The MySQL CURDATE() function will fetch the current date and time from the system clock of the MySQL server. Since the function is only conformed to yield the current date as the value, it will extract the current date and returns it in the YYYY-MM-DD format by default.

MySQL's current date has several applications in various scenarios. For instance, it can be used in places where we need to show the days countdown from the server side. Alternatively, we can use it to calculate age based on a person's date of birth. It can be used with the WHERE clause and for selecting or updating a query as well.

But, you must have come across other terms in MySQL such as CURRENT_DATE, CURRENT_DATE() along with CURDATE() functions. Is there a difference?

As far as the result is concerned, no.

The CURDATE() and CURRENT_DATE() are synonyms of each other and have the same functionality. The only difference is that the CURDATE() is a standard MySQL function and is supported by all the versions of MySQL whereas CURRENT_DATE() is supported by the versions MySQL 4.0 and earlier.

Output:

All three functions return the current date in the format YYYY-MM-DD and it will be a DATE data type.

In addition, if you need the current date as well as the time in the result, you can use MySQL's NOW() function. It returns the value as a string in the format YYYY-MM-DD HH:MM:SS.

Output:

Examples

Example 1: Retrieve the current date with MySQL CURDATE() function

We'll simply be calling the CURDATE() function with a SELECT statement to get the current date. Code:

Output:

Example 2: Retrieve date using MySQL CURDATE() function with numeric value included

What if we add or subtract any numeric value from the CURDATE() function? It will fetch the date from the day as specified concerning the numeric value added. As the outcome in the previous example was 2023-03-06, we'll get 2023-03-10 if we add 4 to the function or 2023-03-04 if we subtract 2.

Code:

Output:

Code:

Output:

The numerical value included with the CURDATE() function is the number of days added or subtracted from the current date.

Exceptional Cases While Performing Arithmetic Operations

In the above two cases, the added or subtracted numeric values were still in the range of the current month's date. What if we add or subtract 30 to CURDATE() straight away? There will be an unexpected output.

There will be an incorrect output in this case:

The correct way is to use DATE_ADD() or DATE_SUB() with the CURDATE() function to perform any arithmetic operations.

Moreover, the output will be in the YYYY-MM-DD format.

Alternatively, you can use DATE_SUB() instead of DATE_ADD() if you need to subtract the numeric value from the CURDATE() function.

Example 3: Retrieve all the products ordered today using MySQL CURDATE() function

Let's first create a table ORDERS with the columns order_id, order_date, product_name, and price.

Code:

Now, let's insert some orders into the table.

Now, if we want to check the orders received today, we can do so with the help of the CURDATE() function in our query like this:

Output:

We are good to go with a good example case now.

Example 4: Output the remaining days of subscription for all users

Let's have a table of users with the columns customer_name, subscription_date, and package_name.

Code:

Here's the structure of the table:

Now, insert some records in this table.

Now, use the SELECT statement to see all the records.

Output:

Based on the date of subscription, we'll try to find out the remaining number of days for the package and if it's already been more than a year since the user had subscribed, then show it as EXPIRED. For instance, if the current date is 2023-03-06 and the date of the subscription was 2023-01-03, then the remaining days will be 303.

See Also

To get a good grasp on the functions related to date and time, learn about these functions as well: 

• NOW()
• DATEDIFF()
• DATE_ADD()
• DATE_SUB()

MCQs

a. What does the CURDATE() function return in MySQL?

1. The current date and time
2. The current date in the format YYYY-MM-DD
3. The current time in the format HH:MM
4. None of the above

Correct answer: 2

b. What will be the output of the following code if the current date is 2023-03-07?

1. 20230322
2. 2023-03-22
3. 22-03-2023
4. None of the above

Correct answer: 1

c. Which of the following functions will return the current date and time?

1. CURDATE()
2. CURRENT_DATE()
3. NOW()
4. None of the above

Correct answer: 3