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.