what is wrong with this query using str_to-date function?

Question

SELECT STR_TO_DATE(SUBSTRING_INDEX(`REPORTDATETIME`,' ',1),'%m/%d/%y') 
FROM crimes 
where REPORTDATETIME like '%1/12/2001%'

it is the query which iam using reportdatetime(varchar) is the column name of table

reportdatetime
1/12/2001 1:30
12/23/2003 1:09
11/12/2001 1:30
5/23/2003 1:09

the result which query gives

2020-1-12

but the result iam expecting is 2001-1-12

You main problem is that your STR_TO_DATE format should be '%m/%d/%Y' for a four-digit year. — phatfingers, Nov 27 '15 at 21:45

score 1 · Answer 1 · answered Nov 27 '15 at 21:51

1

Your STR_TO_DATE format should be '%m/%d/%Y' for a four-digit year.

Other peripheral issues to note... I would expect your WHERE clause to catch two different rows (always best to avoid using a leading '%' in LIKE whenever you can). Also, a time of 1:90 looks very strange.

answered Nov 27 '15 at 21:51

phatfingers

9,770
3
30
44

opps do not worry about time it is dumy.i am skipping time and only getting date – usama mirza Nov 28 '15 at 07:43

what is wrong with this query using str_to-date function?

1 Answers1