D.Rush D.Rush - 2 years ago 71
SQL Question

How can I combine a datepart and a literal in a SQL Where clause?

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')

I get an "Operand type clash: date is incompatible with int" error.

Answer Source

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 - in -09-01 and that's probably what thrown you off the track here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download