CODEWITHSUNDEEP
mysqlsql
Muhammad
Muhammad

Reputation: 307

How to compare a String to Date in MySQL?

I have 2 tables table1 with column tran_date datatype datetime and table2 with column tran_date datatype varchar(50).

in table2 the tran_date has values like 1/1/2000.

now how to compare these 2 columns?

I tried the following:

select * 
from table1 t1, table2 t2
where CAST(t1.tran_date AS DATETIME) = CAST(t2.tran_date AS DATETIME)

Regards:

Upvotes: 0

Views: 70

Answers (2)

Itay Grudev
Itay Grudev

Reputation: 7434

MySQL simply has a function called STR_TO_DATE() which coverts a string into a Date which you can then use to compare it with another date.

Here is an example:

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

Note that it requires the date format to be specified (second argument). Check the STR_TO_DATE() reference for details.

Hope it helps.

Upvotes: 1

tchow002
tchow002

Reputation: 1098

Use STR_TO_DATE()

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

Upvotes: 0

Related Questions