Mysql

Simple Example of MySQL Date Comparison

This tutorial helps to understand MySQL Date Comparison with examples. MySQL offers a rich set of functions and operators to handle date and time data. We’ll go through working with dates in date comparison.

There are a number of conditions in web applications where we need to to filter, sort, and manipulate data based on date-related conditions.

There are the following date and time data types available in MySQL:

  • DATE: This type stores date values in the format ‘YYYY-MM-DD’.
  • TIME: TIME stores time values in the format ‘HH:MM:SS’.
  • DATETIME: DATETIME stores both date and time in the format ‘YYYY-MM-DD HH:MM:SS’.
  • TIMESTAMP: Similar to DATETIME, TIMESTAMP stores date and time, but it has a different range and automatic update behavior.
  • YEAR: YEAR stores a year value in the format ‘YYYY’.

MySQL Date Comparison Operators

Let’s discuss one by one all mysql date operators with examples.

MySQL date EQUALS

The EQUALS (=) operator is used to find records where two date values are equal.

SELECT * FROM employee WHERE joining_date = '2023-10-15';

MySQL date NOT EQUALS (<> or !=)

The NOT EQUALS (<> or !=) operator is used to find records where two date values are not equal.

SELECT * FROM employee WHERE joining_date != '2023-10-15';

MySQL date LESS THAN (<)

The LESS THAN (<) operator is used to find records of those joining dates earlier than a specified date.

SELECT * FROM employee WHERE joining_date > '2023-10-15';

MySQL date GREATER THAN (>)

The GREATER THAN (>) operator is used to find records of those joining dates later than a specified date.

SELECT * FROM employee WHERE joining_date > '2023-10-15';

LESS THAN OR EQUAL TO (<=) Or GREATER THAN OR EQUAL TO (>=)

We can also use LESS THAN OR EQUAL TO (<=) operator to get records that are earlier than or equal to a specified date.

GREATER THAN OR EQUAL TO (>=) operator to find records which are later than or equal to a specified date.

Related Post
SELECT * FROM employee WHERE joining_date <= '2023-10-15';
SELECT * FROM employee WHERE joining_date >= '2023-10-15';

MySQL Date Functions for Comparison

MySQL offers various functions to manipulate and compare date values.

How To Find Date Difference Using DATEDIFF()

The DATEDIFF() function is used to calculate the number of days between two date values.

SELECT DATEDIFF('2023-10-30', '2023-10-15') AS days_between;

How To Add Number of Days in Mysql Date

The MySQL DATE_ADD() function is used to add a specified number of days to a date.

SELECT DATE_ADD('2023-10-15', INTERVAL 7 DAY) AS new_date;

How To Subtract Day from a date in MySql

You can DATE_SUB() method to subtract a specified number of days from a date.

SELECT DATE_SUB('2023-10-15', INTERVAL 3 MONTH) AS new_date;

How To Format Date in MySQL

The MySQL offers DATE_FORMAT() method to format a date as a string according to a specified format.

SELECT DATE_FORMAT('2023-10-15', '%M %d, %Y') AS formatted_date;

How to handle NULL Values in MySQL

You can check if a date column contains NULL values by IS NULL or IS NOT NULL operators to filter records based on date presence.

SELECT * FROM employee WHERE joining_date IS NULL;
SELECT * FROM employee WHERE joining_date IS NOT NULL;

WHats is Difference Between CURRENT_TIMESTAMP and NOW()

The CURRENT_TIMESTAMP is a SQL-standard function that retrieves the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’.

Its have auto-update column features so It will automatically update whenever a new record is inserted or an existing record is updated.

The Now() is a MySQL-specific function that retrieves the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’. It does not provide the auto-update feature.

Conclusion:

The date comparison is a common operation for anyone working with databases containing date and time data. In this article, we’ve covered the essential concepts, operators, and functions for comparing dates in MySQL.

Recent Posts

Configure and Retrieve S3 Information Using Laravel PHP-AWS-SDK

This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More

3 months ago

What is the Purpose of php_eol in PHP?

in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More

9 months ago

Laravel Table Relationship Methods With Example

This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More

9 months ago

Exploring the Power of Laravel Eloquent Join?

We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More

9 months ago

Quick and Easy Installation of Laravel Valet

in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More

10 months ago

What is Laravel Soft Delete and How Does it Work?

I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More

10 months ago

Categories