Getting a SELECT statement to work with STR_TO_DATE

Question

I have dates stored as strings in a table (column name is 'log_event') - yeah, I know it should be a real date type - but I'm working with an existing table that I didn't create, etc.

Anyway, dates are in the column in the form of

'08/14/2014 1:01 am' (or pm)

I'm really just trying to get a SELECT based on the date portion of the string (I don't care about the time for the purpose of my SELECT, I just need all rows that have a date of between say date1 and date2), and I've tried:

SELECT * FROM db_event_log WHERE STR_TO_DATE(log_event,'%m/%d/%Y') > STR_TO_DATE('08/01/2014', '%m/%d/%Y')

but my SELECT returns NULL

(Yes, I know this doesn't SELECT between two dates - I'm just trying to build a query string step-by-step to see that I've got it right, before trying to go to the next level)

I've also tried to format for the time as well by doing:

SELECT * FROM db_event_log WHERE STR_TO_DATE(log_event,'%m/%d/%Y %l:%i %p') > STR_TO_DATE('08/01/2014 12:00 am', '%m/%d/%Y %l:%i %p')

which also returns a NULL selection.

Where am I going wrong?

Works for me: http://sqlfiddle.com/#!2/59da0/1 Perhaps your data is not in the format you expect. — Moob, Sep 29 '14 at 16:10
DOH!! Typo on the field name. Sorry - I feel so stupid at this point. Should have been log_event1. sheesh. — TDU, Sep 29 '14 at 18:10
However Moob - your code was exactly what I was looking for. Select between two dates. Works just fine. Thanks much! — TDU, Sep 29 '14 at 18:10
Glad to help. Whilst I appreciate your gratitude, please note that comments are not really the place for thanks or chat. If you feel that an answer is useful and well-researched you should up-vote it. If you believe an answer to be the best solution to your problem you can choose to mark it as your accepted answer should you wish. More info at http://stackoverflow.com/help/someone-answers — Moob, Sep 29 '14 at 19:47

score 0 · Answer 1 · answered Sep 29 '14 at 16:01

Try this:

 DATE(STR_TO_DATE(log_event,  '%m/%d/%Y %l:%i %p'))

STR_TO_DATE() returns NULL when it can't make sense of its entire input string, so I suggest you interpret the whole thing and then use DATE() to get the date part.

You can also do your date-range selection without getting the date part from each of your text timestamps. This will get yesterday's entries, for example

 WHERE STR_TO_DATE(log_event,  '%m/%d/%Y %l:%i %p') >= DATE(NOW()) - INTERVAL 1 DAY
   AND STR_TO_DATE(log_event,  '%m/%d/%Y %l:%i %p') <  DATE(NOW())

By the way, this insistence on returning NULL can be helpful when you're cleaning your data. When troubleshooting you can give a command like this to find date strings that don't make sense.

 SELECT whatever FROM table WHERE STR_TO_DATE(log_event,  '%m/%d/%Y %l:%i %p') IS NULL

and you'll see your "bad" data.

score 0 · Answer 2 · answered Sep 29 '14 at 16:29

Your code works for me...

I set up the following schema on sqlfiddle.com:

CREATE TABLE db_event_log (
    id INT(11) NOT NULL AUTO_INCREMENT,
    log_event VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO db_event_log (log_event) 
VALUES ('07/14/2014 1:01 am'),
       ('08/14/2014 1:01 am'),
       ('09/14/2014 9:01 pm');

...then ran your query:

SELECT * FROM db_event_log 
WHERE STR_TO_DATE(log_event,'%m/%d/%Y') > STR_TO_DATE('08/01/2014', '%m/%d/%Y')

...which returned:

ID  LOG_EVENT
2   08/14/2014 1:01 am
3   09/14/2014 9:01 pm

http://sqlfiddle.com/#!2/59da0/1

Getting Results Between Two Dates

To get results between two dates you can use MySQL's BETWEEN operator. EG:

SELECT * FROM db_event_log 
WHERE STR_TO_DATE(log_event,'%m/%d/%Y') 
BETWEEN STR_TO_DATE('08/01/2014', '%m/%d/%Y') AND STR_TO_DATE('09/01/2014', '%m/%d/%Y')

http://sqlfiddle.com/#!2/cc7681/1

Getting a SELECT statement to work with STR_TO_DATE

2 Answers2

Getting Results Between Two Dates