SELECT *  FROM ActivityDue
WHERE StatusDate >= CONVERT(SMALLDATETIME,'10/01/' + cast( Year(getdate())-1 as char(4) ),102)
AND StatusDate < CONVERT(SMALLDATETIME,'10/01/' + cast(Year(getdate()) as char(4) ),102)


This works for me
 

  David.Harding@s... wrote: 

I've tried looking through the archieve and can't find what I'm looking 
for so I thought I would ask the experts. My company operates on a 
Federal Fiscal Year. My users want to look at all activity during the 
current Federal Fiscal Year. I wrote a function in MS Access and VB 
because you can drop the year off and the date functions assume the 
current year. But when I do it in SQL, I get an error. So I need 
something like this:

SELECT * FROM ActivityDue
WHERE StatusDate >= CONVERT(SMALLDATETIME,'10/01/2000',102)
AND StatusDate < CONVERT(SMALLDATETIME,'10/01/2001',102)

But I do not want to have to edit the 10/01/2000 and 10/01/2001 in my 
query every year. Any ideas?