What I'm trying to do is select * from a date field where the start date is 9-1-last year and end date is 8-31-current year, but I don't want to make the year specific.
Select * from dbo."TABLE"
where (Date_Field) between (year(DATEADD(year,-1, getdate())) +'09-01') and ((YEAR( getdate())) +'08-31')
Tom H got it right. SQL server is smart enough to cast the date from string though, so
SELECT * from dbo."TABLE" WHERE (Date_Field) BETWEEN CONVERT(varchar, YEAR(DATEADD(year, - 1, GETDATE()))) + '-09-01' AND CONVERT(varchar, YEAR(GETDATE())) + '-08-31'
would suffice. More importantly you missed the leading
-09-01 and that's probably what thrown you off the track here.