0

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?

kba
  • 4,190
  • 2
  • 15
  • 24
DMSJax
  • 1,709
  • 4
  • 22
  • 35
  • 3
    Please tag your question with the database that you are using. And, you should really put your effort into fixing the table, rather than writing arcane queries to make sense of a really bad format. – Gordon Linoff May 26 '15 at 15:47
  • Yes, I think the answer to your question is, "possibly, but you're making so much work for yourself by not properly storing your data that it's more trouble than it's worth." Store the creator's name in a varchar field and the date in a date field - that way you can use (I am guessing) MySQL's powerful date management functions. – Geoff Atkins May 26 '15 at 15:54
  • @GeoffAtkins Geoff, InnoDB added, It's a clients database and I'm responsible for only certain things/parts I can inform them all day, but unless they approve it and the cost to change a bad DB structure, then I/We are stuck with what we got. There problems extend well beyond just this question, so its not as simple as just fixing this issue. – DMSJax May 26 '15 at 15:56
  • @DMSJax - Then you're probably better off exploding the data in PHP - get the lot, explode it using a function into an array and then apply your search criteria to the array. If the client complains about performance you can explain exactly what the problem is. – Geoff Atkins May 26 '15 at 15:59
  • Thanks. I'll go that route if no other options present themselves. – DMSJax May 26 '15 at 16:01
  • A guy who created that table must not touch a computer ever again – akuzminsky May 26 '15 at 18:06

1 Answers1

0

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.

Community
  • 1
  • 1
kba
  • 4,190
  • 2
  • 15
  • 24