'How do I fetch the records of those rows which have the maximum date value in mysql?

My sql query:

SELECT * FROM `electric_transaction` 
WHERE `Current Date`=(SELECT MAX(STR_TO_DATE(`Current Date`, '%d/%m/%Y')) 
FROM `electric_transaction`)

When I execute the above query, I get a list of warnings showing:

Warning: #1292 Truncated incorrect datetime value: '01/04/2021'

Warning: #1292 Truncated incorrect datetime value: '31/03/2021'...

I've even tried

 SELECT * FROM `electric_transaction` 
 WHERE `Current Date`=(SELECT MAX(`Current Date`) FROM 
 `electric_transaction`)

But, it just shows all the records which don't even have the maximum date. How do I fetch only those records which have the maximum date?

Note: My Current Date column is of type VARCHAR and the dates are in d/m/Y format

mysql


Solution 1:[1]

As suggested by @Akina this query works:

SELECT * FROM `electric_transaction` 
WHERE `Current Date`=
(SELECT DATE_FORMAT(MAX(STR_TO_DATE(`Current Date`, '%d/%m/%Y')),
 '%d/%m/%Y') FROM `electric_transaction`)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions