MySQL today()

Introduction to MySQL today()

• In MySQL, the Today() function is a built-in MySQL Date function which provides today’s date value where we will be using MySQL stored procedures and functions.
• With this MySQL Today() function, we will learn to create a special type of MySQL stored program by implementing the CREATE FUNCTION query statement.
• In MySQL Today() function we will build a Date function using some of the MySQL Date functions and use it for accessing the single Date value thus called a built-in date utility code.
• Generally, the stored functions in MySQL are applied to summarizemutual formulas or certain rules which can be reprocessed among several SQL statements or stored programs.

Syntax

To get MySQL Today() function to result in today’s date we will use the built-in functions of the date data type. For this, we need to create your own MySQL Today() function as a stored program. As we know CURDATE() function or CURRENT_DATE() function in MySQL provides the current date and we also use it most of the time to retrieve the present date or time in the MySQL queries.

Now, we can replace this MySQL CURDATE() function or CURRENT_DATE() function by MySQL Today() function to make the query statements more comprehensible. This is possible by the succeeding SQL command code structure firstly:

DELIMITER $$     // creating stored function program
CREATE FUNCTION FunctionName(
Parameter1,
Parameter2,….
)
RETURNS Data_Type   // Declare the datatype
[NOT] DETERMINISTIC
BEGIN
RETURN  Replaceable_Stored_FunctionName;   // statements
END$$
DELIMITER ;    // end of the procedure

Explaining the terms of the above syntax below:

• FunctionName specifies the name given to the stored function to be used after the CREATE FUNCTION Keyword that we want to name and create it.
• Followed by the stored function name we need to put the list of all required parameters that we want to mention within the parentheses. All these parameters are the MySQL IN parameters by default so, we cannot state OUT, INOUT or IN modifiers to those parameters.
• With the RETURNS statement, we will specify the valid MySQL Data type for which we are creating the stored program function.
• Then, we must state the DETERMINISTIC keyword to make the function either deterministic or not. In a deterministic function, it also returns the identical result for similar parameters as inputs whereas in a non-deterministic one it outputs different values for similar parameters as inputs. But MySQL uses by default NOT DETERMINISTICkeyword option if we do not set anyone.
• Between the BEGIN and END keyword blocks, we can write the code in this body for the stored function. In the body block section, at least a single RETURN query statement should be mentioned which returns the result value to the calling function.On execution, the stored function terminates instantly if the RETURN query is visited.

After running the proper stored-program syntax above, with valid names of function, parameters and data type stored function will be created on the MySQL database. We can call the newly built stored program with the following MySQL query and execute the output as desired:

SELECT FunctionName();

Here, the FunctionName is the term mentioned in the above syntax of stored program function.

How MySQL Today() Function work?

Let us consider to access today’s date by implementing the built-in date functions to provide the present date in MySQL database.

Sometimes, from the database table rows we want to fetch data having date column with Date data type is today, and then the query will be written as:

SELECT ColumnName FROM TableName WHERE ExpiredDate = Today;

To acquire today’s date, we can use either the CURRENT_DATE() or CURDATE() function with the query as follows:

SELECT CURRENT_DATE() Today;

Output:

Also, you can use NOW() function or SYSDATE() function to select the date part from the present date-time resulted by the function as shown follows:

SELECT DATE(SYSDATE()) Today;

Output:

Now, we can alter the query with the following one:

SELECT ColumnName FROM TableName WHERE ExpiredDate = CURRENT_DATE();

But suppose if the column ExpiredDate includes both date and time types, then you can apply DATE() in MySQL to get them just the date value and equate it with the current value of date:

SELECT ColumnName FROM TableName WHERE DATE(ExpiredDate) = CURRENT_DATE();

Examples to Implement Today() function in MySQL

To understand the MySQL Today(), we can replace the CURRENT_DATE() function when we use this function many times in the queries.

For this, let us create a stored function named as Today() in MySQL with the syntax stated below:

DELIMITER $$
CREATE FUNCTION Today()
RETURNS DATE
BEGIN
RETURN  CURRENT_DATE();
END$$
DELIMITER ;

You need to execute the above command in the MySQL database and then, you will find that the Today() function is created as a stored program and we can call it now and use it in the query statement.

To implement the created program let us run the succeeding query having Today() function along with the MySQL SELECT keyword and fetch the current value of date:

SELECT Today()Today_Date;

Output:

Now, with this MySQL Today() function which is built-in date function with the stored program to output the present date, we can use it to fetch the date of the next day i.e. tomorrow or more upcoming days. The query can be written as simple as the code below to get tomorrow’s date or the day after tomorrow’s date:

SELECT Today() + interval 2 day Day_after_tomorrow_date;

Output:

Here, the interval keyword is used to determine the interval value and unit.

Again, let us consider to fetch the data which is of either the previous days or say yesterday using the MySQL Today() function as follows:

SELECT Today() -interval 2 day Day_before_yesterday_date;

Output:

Conclusion

Likewise, we can also learn and develop the desired function of date or other valid data types in MySQL by applying for the stored program and execute it and use it in our MySQL queries. Thus, MySQL Today() function is an exceptional stored program, created and queried to get the current date values and use it in the MySQL statements to provide the output.

Recommended Articles

This is a guide to MySQL today(). Here we discuss an introduction to MySQL today(), syntax, how does it work with query examples. You can also go through our other related articles to learn more –

1. MySQL Timestamp
2. MySQL Avg()
3. MySQL MAX() Function
4. NOT In MySQL