CODEWITHSUNDEEP
mysqlsqlselectsubstringdate-format
user3156202
user3156202

Reputation: 13

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!

Upvotes: 1

Views: 2211

Answers (5)

duellsy
duellsy

Reputation: 8595

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'

Upvotes: 2

Deepu Sasidharan
Deepu Sasidharan

Reputation: 5309

use the below

substr(posts.datestr,3)

instead of 

 SUBSTRING(posts.datestr, 4, 8)

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29071

Use DATETIME function instead of SUBSTRING()

SELECT users.id, DATE_FORMAT(STR_TO_DATE(posts.datestr, '%d-%b-%Y'), '%b-%Y') updatedDate
FROM users, posts 
HAVING updatedDate = 'Jan-2014'

Upvotes: 0

Gopal Joshi
Gopal Joshi

Reputation: 2358

Use Inner JOIN

SELECT users.id, SUBSTRING(posts.datestr, 4, 8) FROM users inner join posts on users.userid = posts.[primary key] WHERE posts.datestr='Jan-2014'

Below Link can help you http://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 0

chanchal118
chanchal118

Reputation: 3657

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

Upvotes: 0

Related Questions