Date function problem

[mdr]

I want to get sales from the past three months.
I use a variable to get todays date:

declare @Today datetime
set @Today=getdate()

and then try and pass that through to this formula:

and datetable.datevalue between @Today and month(dateadd(m,-3, @today))

However, i am not getting any results.

Can anyone help?

 

[skicamel]

Looks like you just need to flip the dates...

datetable.datevalue between month(dateadd(m,-3, @today)) and @today

 

[JamesLean]

Yep, the range you set for the BETWEEN keyword must go from lowest to highest.

--James

 

[SQLBill]

And why are you changing todays date (getdate()) into a variable? It's not necessary, this will work:

datetable.datevalue between month(dateadd(m,-3, getdate())) and getdate()

-SQLBill

 

[JamesLean]

Actually, there is an error in your expressions. Using the month function will return a simple integer value, so you are basically saying:

WHERE datevalue BETWEEN 3 AND '9th June 2003'

...which is not going to work!

What are you trying to achieve with the MONTH function?

--James

 

[SQLBill]

Use this instead:

datetable.datevalue between dateadd(mm,-3, getdate()) and getdate()

I ran:

select dateadd(mm,-3,getdate())
and it returned:

2003-03-09 11:56:51.663

-SQLBill