DATEADD not working as expected

[SyntaxTerror]

I'm writing a report that will go back from the current date and break it up into weekly periods, so I wanted to use DATEADD to achieve this by doing something like...

DECLARE @CurrDate as TimeStamp

SELECT CASE WHEN CreatedDate BETWEEN DATEADD(D, -7, @CurrDate) and DATEADD (D, 1, @CurrDate) THEN 'pass' ELSE 'fail' END AS 'Last Week'

...then I'd do another case for D, -14 and D, -7 or something like that.

It keeps spitting out "fail", but when I actually replace the DATEADD function with the expected dates, it spits out "pass".

What am I doing wrong?

Any and all help is appreciated! Thank you!

 

[gmmastros]

Don't use TimeStamp. Not now, not ever. It doesn't mean what you think it does. Instead, use DateTime, and also make sure you set it to a value before using it.

ex:

DECLARE @CurrDate as DateTime

Set @CurrDate = GetDate()

SELECT CASE WHEN CreatedDate BETWEEN DATEADD(D, -7, @CurrDate) and DATEADD (D, 1, @CurrDate) THEN 'pass' ELSE 'fail' END AS 'Last Week'

Also, be aware that GetDate() returns date and time, so You'll want to be careful about that. If you explain to us what your definition of a week is, we can probably help with that too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

I love you! <3 That fixed it. Thanks!

The coworker helping me was under the impression that you didn't have to set DateTime to anything (that it would just pull the current date), so that explains why it never worked.

I still steer clear of TimeStamp in the future as well

 

[markros]

If you're using SQL Server 2008, then

declare @CurDate date

set @CurDate = cast(getdate() as date)
-------------

For prior versions of SQL Server

declare @CurDate datetime

set @CurDate = dateadd(day,datediff(day,'20100101',Getdate()),'20100101')-- to strip the time portion of the datetime value. You can use any valid date as a 'base' date in this transformation

PluralSight Learning Library

 

[SyntaxTerror]

Thank you markros! That will come in very handy I'm sure.

What a great community. ...I really appreciate the rapid replies!