How to compare a uk date in mysql

Question

Hi, I have to do a check that a date stored in a database in UK date format dd/mm/yyyy is greater than a given date, e.g 01/01/2010 - what is the best way to go about this? Thanks

Answer 1

I'm assuming you have your dates stored as a string. In that case you may want to use the STR_TO_DATE() function:

SELECT * 
FROM   your_table
WHERE  STR_TO_DATE(your_field, '%d/%m/%Y') > '2010-01-01';

As @OMG Ponies noted in a comment below, you won't be able to use an index on your column for such a query. Therefore, it would probably be a better idea to convert your dates to use the DATE or DATETIME data types:

-- Add a new DATETIME column
ALTER TABLE your_table ADD COLUMN your_new_field DATE;

-- Fill in your new column
UPDATE your_table SET your_new_field = STR_TO_DATE(your_field, '%d/%m/%Y');

-- Drop your old string column
ALTER TABLE your_table DROP COLUMN your_field;

-- Rename your new column to the previous name
ALTER TABLE your_table CHANGE your_new_field your_field DATE;

Then date comparisons will also be much easier:

SELECT * 
FROM   your_table
WHERE  your_field > '2010-01-01';

If you want to display your date using the UK format, you can simply use the DATE_FORMAT() function as follows:

SELECT DATE_FORMAT(your_field, '%d/%m/%Y')
FROM   your_table
WHERE  your_field > '2010-01-01';    

And finally, if you want to compare your date field with a date string formatting as a UK date, you can use the STR_TO_DATE() function again:

SELECT DATE_FORMAT(your_field, '%d/%m/%Y')
FROM   your_table
WHERE  your_field > STR_TO_DATE('2010-01-01', '%d/%m/%Y');