How To Use DATE Functions In MySQL

Introduction

In this tutorial, we will explore some of the date functions available in MySQL. While there are many functions to choose from, but we'll focus on a few that are particularly useful for working with dates and times. By the end of this tutorial, you'll better understand how to manipulate dates in MySQL. Let's get started!

What is MySQL?

SQL is the most common standardized language used to access databases. MySQL is an acronym for My Structured Query Language and a database management system. In this MySQL, SQL is a programming language used to work with data in relational databases.

DATE() function in MySQL

DATE is used to store the value of the date. Using the DATE() function in MySQL, you can precisely extract the date from the Datetime datatype column. This DATE() is used to handle Date efficiently. Some functions of the Date Functions are CURDATE, DATEDIFF, NOW, and DAYTIME.

Let's understand these functions in detail with an example

1. CURDATE()

This is used to get the current date in the format of YYYY-MM-DD. We can also use CURRENT_DATE() instead of CURDATE(), as this is the synonym of the CURDATE() function.

Syntax

Select current_date(), curdate();

Example

Select current_date(), 
curdate();

2. DATEDIFF()

This is used to get the difference between the two dates. Simply it means we can calculate the difference between two dates.

Syntax

SELECT DATEDIFF('day1','day2');

Example

SELECT DATEDIFF('2023-12-31','2023-01-01') AS Total_days,  
       DATEDIFF('2023-03-31','2023-03-26') AS Days_left;

3. NOW()

This is used to get the current date and time in the YYYY-MM-DD HH:MM:SS format.

Syntax

SELECT now();

Example

SELECT now();

4. DAYNAME()

This is used to get the day's name on a particular date.

Syntax

SELECT dayname();

Example

SELECT dayname("2023-03-27") as Today,
 dayname("2023-12-31") as Last_day;

Conclusion

By using these functions, you can get the current date and time, calculate the difference between two dates, get the name of the day on a particular date, and extract the date from the DATETIME data type column. I hope this tutorial helped expand your knowledge of DATE functions in MySQL. 

Thank you for reading.