Search code examples
mysqldate-conversion

varchar date conversion to max() date() mysql

In my database the column type is varchar(30) which stores date (24/02/2018), having multiple records. i want the maximum date e.g i have 10/01/2016, 20/03/2017, 24/02/2018. I am using the below query:

SELECT MAX(receipt_date) as rd FROM tblname

which returns me 10/01/2016 which is wrong. i also tried to convert it to date format. but failed. mysql gives me syntax error.

SELECT CONVERT(varchar, mycolumn, 105) FROM tblname

but failed.

Solution

  • This is what I'd do:

    ALTER TABLE tblname ADD COLUMN receipt_date2 DATE;

UPDATE tblname SET receipt_date2 = STR_TO_DATE(receipt_date, '%d/%m/%Y');

ALTER TABLE tblname DROP COLUMN receipt_date,
  CHANGE receipt_date2 receipt_date DATE;

SELECT MAX(receipt_date) AS rd FROM tblname;

    You can't use dates effectively in MySQL if you store than as VARCHAR in dd-mm-yyyy format. You should use a DATE data type. MySQL's DATE data type stores dates in yyyy-mm-dd format only. This way it can search for MAX() easily, it can sort them, it can do date arithmetic.