MySQL comparing date string into date column

Question

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.

you shouldn't need to cast it. MySQL recognizes strings as dates if they are in ISO format. You may need to convert your 9:44 PM to 24-hour time though. — Cfreak
– Cfreak, Commented Nov 17, 2014 at 1:28
What version of MySQL? Before 5.6.4 fractional seconds weren't supported in DATETIME variables. What is the data type of cdate? — O. Jones
– O. Jones, Commented Nov 17, 2014 at 1:37

O. Jones · Accepted Answer · 2014-11-17 02:04:49Z

2

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.

edited Nov 17, 2014 at 2:04

answered Nov 17, 2014 at 1:47

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

Comments

sunysen · Accepted Answer · 2014-11-17 01:47:43Z

-1

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')

edited Nov 17, 2014 at 1:47

answered Nov 17, 2014 at 1:38

sunysen

2,3711 gold badge15 silver badges13 bronze badges

4 Comments

user3094947 Over a year ago

but the greater then operator doesn't work with the time

O. Jones Over a year ago

Ummm, you lost the PM in this answer.

sunysen Over a year ago

please try @user3094947

Spencer Wieczorek Over a year ago

I recommend explaining what your code snippet does.

Collectives™ on Stack Overflow

MySQL comparing date string into date column

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related