Search code examples
mysqlsqlselectsubstringdate-format

MySQL Substring with WHERE

I've searched over the internet but couldn't find a way to resolve my issue. My query is something like that

SELECT users.id, SUBSTRING(posts.datestr, 4, 8) 
FROM users, posts 
WHERE posts.datestr='Jan-2014'

(That query above was shortened)

The value in posts.datestr is actually 20-Jan-2014, but I want it to be Jan-2014 instead, but even after adding that SUBSTRING code, it still somehow shows as 20-Jan-2014.

Any idea what I did wrong?

Thanks!

Solution

  • You need to alias the result of the SUBSTRING and use that alias in your conditions (shortdate in the example below)

    Since you'll now be using an alias in your conditions, you can't use the where clause, you can though use the having clause

    something like:

    SELECT users.id, SUBSTRING(posts.datestr, 4, 8) as shortdate 
FROM users, posts 
HAVING shortdate='Jan-2014'