MySQL CURDATE() and CURRENT_DATE() – Print Current Date

By /
CURDATE And CURRENT DATE

In this tutorial, we will learn MySQL CURDATE() and CURRENT_DATE() function. Suppose you are a teacher and you want to make a column called LastDatePresent in a Students table. The LastDatePresent stores the last date the student attended class. Every student attended today’s class.

Your job as a teacher is to fill this LastDatePresent column with today’s date. For such operations, MySQL provides us with the CURDATE() and CURRENT_DATE() functions. The CURDATE() and CURRENT_DATE() functions are used to return the current date. They do the same thing, the only difference is in their names. Let us look at the syntax of them followed by a few examples.

Syntax of MySQL CURDATE()

CURDATE()Code language: SQL (Structured Query Language) (sql)

Note that, no parameters or arguments are passed to the CURDATE() function.

Syntax of MySQL CURRENT_DATE()

CURRENT_DATE()Code language: SQL (Structured Query Language) (sql)

Or,

CURRENT_DATECode language: SQL (Structured Query Language) (sql)

Note that, no parameters or arguments are passed to the CURRENT_DATE() function. CURRENT_DATE() can also be used simply as CURRENT_DATE.

Examples of MySQL CURDATE() and CURRENT_DATE()

Let us kick things off with a very basic example. How about returning the current date? We write the below query for it using the SELECT Statement.

SELECT CURDATE();Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL CURDATE Basic Example

Similarly, we can do this using the CURRENT_DATE() function too. The query is –

SELECT CURRENT_DATE();Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL CURDATE Current Date Basic Example

MySQL CURDATE() and CURRENT_DATE() In Numeric Context

In the previous example, the output returned by CURDATE() and CURRENT_DATE() was in string context. Let us see how we can return the output for these functions in a numeric context. The numeric context output just eliminates the dashes between the dates and returns the date in the format – YYYYMMDD. The query with CURDATE() is –

SELECT CURDATE() + 0;Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL CURDATE Numeric

And the one with CURRENT_DATE() is –

SELECT CURRENT_DATE() + 0;Code language: SQL (Structured Query Language) (sql)

We get the output as –

Current Date Numeric

MySQL CURDATE() vs CURRENT_DATE()

As I have iterated before, CURDATE() and both syntaxes of CURRENT_DATE() do the same thing – return the current date. Let us demonstrate this using the below query.

SELECT CURRENT_DATE, CURRENT_DATE(), CURDATE();Code language: SQL (Structured Query Language) (sql)

And the output is –

Curdate Vs Current Date

Finding Yesterday, Tomorrow and Day after tomorrow

We can use MySQL CURDATE() and CURRENT_DATE() to find the values of the previous day, the next day, the day after tomorrow and more. All we have to do is add or subtract the number of days you want to go forwards or backwards. Let us see an example. Consider the below queries with CURDATE().

SELECT CURDATE() - 1 AS Yesterday; 
SELECT CURDATE() + 1 AS Tomorrow; 
SELECT CURDATE() + 2 AS DayAfterTomorrow;Code language: SQL (Structured Query Language) (sql)

We have used aliases to make our output more readable. Subtracting 1 from the current date will give you the value of the previous day. In the same way, adding one and two to the current date will give you the values of the next day and the day after that respectively. The output is –

Curdate Currentdate Expression1

We can do the same using CURRENT_DATE() as follows –

SELECT CURRENT_DATE() - 1 AS Yesterday; 
SELECT CURRENT_DATE() + 1 AS Tomorrow; 
SELECT CURRENT_DATE() + 2 AS DayAfterTomorrow;Code language: SQL (Structured Query Language) (sql)

And the output is –

Curdate Currentdate Expression2

Working With Tables

Consider the below Students table.

Outer Join Students Table
Students Table

Now refer to the problem we saw in the beginning. Suppose you are a teacher and you want to make a column called LastDatePresent in the above Students table.

The LastDatePresent column stores the last date the student attended class. Every student attended today’s class. So, your job as a teacher is to fill this LastDatePresent column with today’s date.

Well, now you know how to go about this and you can get it done using either the CURDATE() or CURRENT_DATE() function in MySQL.

Let us use MySQL CURDATE() to do this. The query gets more concise when we use the CURDATE() function. Since we have to create the LastDatePresent column and add the current date value to it, we will also use the ALTER and UPDATE statements. The queries are – 

ALTER TABLE Students ADD LastDatePresent date; 
UPDATE Students SET LastDatePresent = CURDATE(); 
SELECT * FROM Students;Code language: SQL (Structured Query Language) (sql)

We use the ALTER statement to create a column named LastDatePresent of the type date. We use the UPDATE statement to set all values in it to the current date using the CURDATE() function and finally, we use the SELECT statement to display the newly updated table. The output is –

MySQL Curdate Table Example1

Conclusion

You will find yourself using the MySQL CURDATE() and CURRENT_DATE() functions quite often while working with databases. They have many applications – one of the most important ones is while storing and displaying the date of an invoice that is generated after you have paid the money. 

References