VARCHAR列值的日期比较

Question

I have a SQL table where one of the columns 'creator' is a VARCHAR data type. That column stores values like this one: Mike Jones|05-17-2015 1:21 pm consisting of the person that created the record, the date and time it was created.

I need to use the date portion of that varchar data to form a SQL query, so that we can generate a list of tickets created between two dates. We can't restructure the table easily so i'm stuck working this out in its current format, otherwise we just would have added a CREATED date field and compared on that.

The SQL being attempted is this: SELECTticknumb,cnames,creator,ticktype,tickstatusFROMaticketsWHEREcreator>= '%05-24-2015%' ANDcreator<= '%05-30-2015%' ANDtickstatusNOT LIKE 'Closed,Complete'

Is there a way to do this directly within the SQL query without having to to a looped explode in the php first to extract and compare the earliest / latest dates?

Answer 1

Here is SQL snippet how you can convert your value in varchar column to date type.

SELECT STR_TO_DATE(SUBSTRING_INDEX(`creator`, '|', -1), '%m-%d-%Y %h:%i %p')

http://sqlfiddle.com/#!9/710a2/7/0

Another inspiration

how to convert a string to date in mysql?

How to split the name string in mysql?

I hope it is what you are looking for.