MySQL WHERE error

[neo926]

SELECT DISTINCT(DATE_FORMAT(submitted,'%M')) AS mymonth , DATE_FORMAT(submitted,'%Y') AS myyear , title FROM secondcitysaint WHERE myyear = '2007' ORDER BY submitted DESC

 

#1054 - Unknown column 'myyear' in 'where clause' 

 

I created this temporary column called 'myyear' with the AS function above, and I want to pull data based on a certain year that's listed in the 'myyear' column, but MySQL tells me the column isn't there.  Is there a way I can fix this?

[neo926]

Sorry, I'm using SQL 4.1

[teng84]

can you paste the exact error

[neo926]

I did, the second code box.  Here's the full message:

 

Error

SQL query: Documentation

SELECT DISTINCT (
DATE_FORMAT( submitted, '%M' )
) AS mymonth, DATE_FORMAT( submitted, '%Y' ) AS myyear, title
FROM secondcitysaint
WHERE myyear = '2007'
ORDER BY submitted DESC
LIMIT 0 , 30

MySQL said: Documentation
#1054 - Unknown column 'myyear' in 'where clause' 

[teng84]

try to remove the where clause first and see what happen

[neo926]

It works fine without the WHERE clause, it's just when I add it in

[teng84]

i guess you have to fallow what ure DB wants your code should work but maybe all you can do is not to use that alias and rely on the real tbl

[neo926]

Well, I'd be more than happy to just pull the year from my submitted column, but I don't know how to do that, since it's in DATE_TIME format.

[teng84]

have you tried using year something like year(date here)

[dcp]

If your data is in the datetime format (YYYY-MM-DD HH:MM:SS), you could probably use "LIKE" to get just the 2007 stuff:

 

SELECT DISTINCT (

DATE_FORMAT( submitted, '%M' )

) AS mymonth, DATE_FORMAT( submitted, '%Y' ) AS myyear, title

FROM secondcitysaint

WHERE submitted LIKE '2007%'

ORDER BY submitted DESC

LIMIT 0 , 30