In a SQl query, how can I retrieve rows entered between today and April 1st of the precedent year?

Good day to everyone!

I am writing a T-SQL que开发者_开发知识库ry that would allow me to retrieve transactions that took place since April 1st of the preceding year. For instance, if I run the query today, it will return all transactions since April 1st, 2010. If I run the query in May 2012, it will return all transactions since April 1st, 2011.

The table has a 'date' format column

I tried:

SELECT *
FROM table
WHERE [Date] BETWEEN (year(getdate()-1) AND getdate()

But it fetches the transactions since January 1st of last year. I cannot make out how to define the month as well, and my research on many forums did not help either.

Any hint is very welcome.

Best regards, El Presidente

Use the dateadd() function and you will be happy.

SELECT * FROM table WHERE [Date] BETWEEN dateadd(year,-1,getdate()) AND getdate()

or for April first (the exact reading of the question but not what was meant)...

SELECT * FROM table WHERE [Date] BETWEEN  cast((year(getdate())-1) as varchar(5))+'-04-01' AND getdate()

why not convert the date to seconds and just do a straight number comparison? select * from table where UNIX_TIMESTAMP(date) > etc etc.