CODEWITHSUNDEEP
phpmysqlsqldatabase
user3707303
user3707303

Reputation: 769

How to compare two DATES having varchar as datatype

I have two tables table1 and table2

table1 having following columns (id,date_reg,status)

date_reg is a varchar data type having date format like this (mm-dd-yyyy).

And table2 having following columns(id,uname,date_reg)

date_reg is a varchar data type having date format like this (mm-dd-yyyy).

I need a matched records with recent date (by compare table1.date_reg with table2.date_reg )

Upvotes: 0

Views: 663

Answers (4)

SubjectCurio
SubjectCurio

Reputation: 4872

You could use MySQL's STR_TO_DATE() function to turn your VARCHAR fields into a valid date you can use for comparison, for example:

STR_TO_DATE(table1.date_reg, '%m-%d-%Y')

Ideally though, you don't want to do this. It's not a solution, it's another problem that's being used to try and cover up an existing problem, your Dates are being stored as VARCHAR instead of as DATE.

You would be far better off changing your import code to import the dates from Excel (Or wherever you are importing from) into a correct DATE format for MySQL. If you are not sure how to do that, I'd suggest creating a new question with the code you're using to import the data, and ask how to format it in the correct way.

Upvotes: 0

CDahn
CDahn

Reputation: 1876

I'm not familiar with regex/text parsing in PHP, but the general strategy would be to split your two strings on '-', then compare the year, then the month, and then the day. In pseudo code:

if table1.year > table2.year:
    return table1.date
else if table1.year < table2.year:
    return table2.date
else:
    if table1.month > table2.month:
        return table1.date
    else if table2.month > table1.month:
        return table2.date
    else:
        if table1.day > table2.day:
            return table1.date
        else if table2.day > table1.day:
            return table2.date
        else:
            return 0

Upvotes: 1

DS9
DS9

Reputation: 3033

use following if date format is same:

select * from table1 left join table2 ON table1.date_reg = table2.date_reg

or if you want to where condition then

select * from table1 left join table2 ON table1.date_reg = table2.date_reg where table1.date='06-05-2014'

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT *
FROM table1,
     table2
WHERE STR_TO_DATE(table1.date_reg, '%m/%d/%Y')=STR_TO_DATE(table2.date_reg, '%m/%d/%Y')

Upvotes: 2

Related Questions