CODEWITHSUNDEEP
mysql
dlofrodloh
dlofrodloh

Reputation: 1744

Using aliased result of function in where clause

I want to pull specific rows from a table where the date matches a certain date. First I'm converting the date string to date format, here's the query:

SELECT id, str_to_date(candidate.AddDate,"%d/%m/%Y") n FROM candidate WHERE n='2016-01-01';

But I get the error "Unknown column 'n' in WHERE clause"

How do I make the query use the result of str_to_date in the where clause?

Upvotes: 0

Views: 44

Answers (2)

Murlidhar Fichadia
Murlidhar Fichadia

Reputation: 2609

I dont know if this is what you are trying to achieve.

SELECT id, adddate from candidate C where C.adddate = "2016-01-01"

Why cant you pull all the table rows where the given date is 2016-01-01. Is this what you want? Or something else. If you have stored the date as date field you dont really need to do str_to_time.

If it is stored as string then 

SELECT * FROM ( SELECT id, DATE_FORMAT(STR_TO_DATE(candidate.adddate, '%d/%m/%Y') x FROM   candidate 
 ) C WHERE  x = '2016-01-01';

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

You cant use the alias on the same level, because isnt created at that time

SELECT id, 
       Str_to_date(candidate.adddate, "%d/%m/%y") n 
FROM   candidate 
WHERE  Str_to_date(candidate.adddate, "%d/%m/%y") = '2016-01-01';

Or create a subquery

SELECT *
FROM (
        SELECT id, 
               Str_to_date(candidate.adddate, "%d/%m/%y") n 
        FROM   candidate 
     ) T
WHERE  n = '2016-01-01';

Upvotes: 2

Related Questions