A WHERE in a Union query

Hi, i have a union query that lists all the years from a date field and add the currentyer if its not already listed:

SELECT DISTINCT Cas Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
ORDER BY DatePart(yyyy,[PressreleaseDate])

what i need to do is filter it with something along the lines of:

WHERE Yearlist LIKE myvariable

although i know i cant simply use:

WHERE Yearlist

it would have to be something like:

WHERE DatePart(yyyy,[PressreleaseDate]) UNION datepart(yyyy, getdate()) LIKE myvariable

Does anyone know how to write this correctly?Not tested it, but something like this:

select YearList from
(select distinct Cas YearList
from dbo.ViewPressReleases
where YearList like myvariable) SomeNameYouLike
union
select datepart(yyyy, getdate())
order by YourOrderField|||ok thanks,

so now i have

select YearList from (select distinct DatePart(yyyy,[PressreleaseDate]) as YearList
from dbo.ViewPressReleases where Yearlist like stgetstyear) mixedyearlist
union select datepart(yyyy, getdate()) order by Yearlist

but i get an error that Yearlist in there WHERE statment is invalid.

any ideas?|||you can't use your alias in the where clause, so you have to reuse the DATEPART function again. Also, your DATEPART returns an integer value. If you want to perform a like with a wildcard character (%) on it, you can use the DATENAME function which returns a string.|||i actually want the integer. This was my origianal probelm; that you cant use an alias in a where clause, but i cant simply use the "datepart..." as its part of a union query and i need to filter the resulting list of the union query, if i've explained myself clearly.|||no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find|||Originally posted by r937
no, it isn't clear, luke

please explain again what the WHERE clause is supposed to find

Ok...

i have

SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])

i want to then filter the resulting list of years by myvariable

so i need to add

WHERE ??? LIKE myvariable

if this was not a union query i would write something like

WHERE DatePart(yyy,[PressreleaseDate]) LIKE my variable

but as it is a union query i asume i have to write somethin like

WHERE (SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate()) ORDER BY DatePart(yyyy,[PressreleaseDate])) LIKE my viable

(which doesnt work) and i know i cant simply use the alias like:

WHERE Yearlist Like myvaiable

so i need to know how to phrase the SQL corrctly in order to filter the list of years.|||no, don't explain in terms of sql, you already tried that ;)

you cannot use LIKE on integers

try explaining it in english

"i want to select only years which ... ?"|||I Think i have it.

SELECT DISTINCT DatePart(yyyy,[PressreleaseDate]) as Yearlist
FROM dbo.ViewPressReleases WHERE DatePart(yyyy,[PressreleaseDate]) LIKE stgetstyear UNION SELECT datepart(yyyy, getdate()) WHERE datepart(yyyy, getdate()) LIKE stgetstyear ORDER BY DatePart(yyyy,[PressreleaseDate])

Thanks.sql