How to compare different format string as date in mysql

Question

I have field a VARCHAR fielddob in mysql table where dates are saved in two format :

1> y-m-d
2> d/m/y

Now I want to select candidates whose dob <= '01/01/1979' (born on or before 01 January, 1979)

but i am unable to understand how to compare these two time formats at once.

is there any way to handle this like either convert one format into another format and then compare.

Perhaps take the time to fix this problem by creating a new DATETIME column and updating it with the string values converted to a DATETIME. — Alex K.
– Alex K., Commented Jul 30, 2018 at 15:25
You should fix the data. But if you can't: where coalesce(date(dob), str_to_date(dob, '%d/%m/%Y')) <= '1979-01-01' — Paul Spiegel
– Paul Spiegel, Commented Jul 30, 2018 at 15:38

Tim Biegeleisen · Accepted Answer · 2018-07-30 15:33:18Z

1

Your current table design is bad because the dob column is:

storing date information as text, and on top of this
storing two different date formats in the same column

You absolutely should fix this long term by storing your date information in a proper date/time column.

Here is one temporary workaround:

SELECT *
FROM yourTable
WHERE
    (dob LIKE '%-%' AND STR_TO_DATE(dob, '%Y-%m-%d') < '1979-01-01') OR
    (dob LIKE '%/%' AND STR_TO_DATE(dob, '%d/%m/%Y') < '1979-01-01');

answered Jul 30, 2018 at 15:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2018-07-30 15:33:26Z

1

I almost hate to write this code, but...

...
where str_to_date(dob, if(dob like '%/%', '%d/%m/%y', '%y-%m-%d')) <= '01/01/1970'

answered Jul 30, 2018 at 15:33

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Comments

CodeLikeBeaker · Accepted Answer · 2018-07-30 19:46:33Z

1

You can also use the IFNULL and STR_TO_DATE on the two date formats:

SELECT
    IFNULL(STR_TO_DATE(col_, '%Y-%m-%d'), STR_TO_DATE(col_, '%m/%d/%Y')) AS 'my_date'
    , col_
FROM
    date_test
WHERE 
    IFNULL(STR_TO_DATE(col_, '%Y-%m-%d'), STR_TO_DATE(col_, '%m/%d/%Y')) <= '1979-01-01';

edited Jul 30, 2018 at 19:46

answered Jul 30, 2018 at 15:37

CodeLikeBeaker

21.4k14 gold badges83 silver badges114 bronze badges

Collectives™ on Stack Overflow

How to compare different format string as date in mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related