CODEWITHSUNDEEP
mysql
user3094947
user3094947

Reputation: 11

MySQL comparing date string into date column

I have this query 

SELECT * FROM tracklogs.sms_outbound
WHERE gsmno = 'rk4@*******.com.ph'
AND cdate > cast('2013/11/14 09:44:48 PM' as datetime)

where cdate format is in %Y-%m-%d %h:%i:%s %p.

I have tried converting the date into that format then cast it as datetime but still doesn't working.

Upvotes: 0

Views: 251

Answers (2)

O. Jones
O. Jones

Reputation: 108766

Use STR_TO_DATE() to correctly convert the datetime literal you have provided to a proper DATETIME value. It seems that your cdate column is a char() or varchar() column. So you will also need to convert that to DATETIME to compare it.

What you need is this:

That works like this (http://sqlfiddle.com/#!2/d41d8/48741/0)

STR_TO_DATE(cdate, '%Y-%m-%d %h:%i:%s %p') >
 STR_TO_DATE('2013/11/14 09:44:48 PM', '%Y/%m/%d %h:%i:%s %p')

Converting these strings to DATETIME data items ensures that the comparison handles both the date and the time correctly. See this fiddle (http://sqlfiddle.com/#!2/d41d8/48743/0)

But, you should consider changing your cdate item to a DATETIME, because then you'll be able to index it and speed up your search.

Upvotes: 2

sunysen
sunysen

Reputation: 2351

SELECT * FROM tracklogs.sms_outbound 
WHERE gsmno = 'rk4@*******.com.ph' 
AND UNIX_TIMESTAMP(str_to_date(cdate,'%Y-%m-%d %H:%i:%s')) > UNIX_TIMESTAMP('2013-11-14 09:44:48')

Upvotes: -1

Related Questions