CODEWITHSUNDEEP
sqlt-sql
Ronald McDonald
Ronald McDonald

Reputation: 2911

Relative date range in T-SQL

I'm trying to select for all items where the createDt is in the last two weeks. I've tried this code but it doesn't work.

SELECT * FROM dbo.mytable
WHERE CreateDt > dateadd(d,-15,CreateDt)

Can someone tell me the correct way to do this?

Upvotes: 4

Views: 5341

Answers (4)

Hogan
Hogan

Reputation: 70523

I believe this is what you want.

SELECT * FROM dbo.mytable
WHERE CreateDt > dateadd(wk,-2,getdate()) AND
      CreateDt < getdate() -- possible bad data

Upvotes: 2

AllenG
AllenG

Reputation: 8190

You're checking to see if CreateDt is Greater than 15 days prior to itself. I'm guessing you're getting a lot more records than you expect.

I would do something like this (for readability, primarily)

Declare @CheckDate DateTime
Set @CheckDate = dateadd(d, -15, GetDate())

SELECT [Columns] from dbo.mytable WHERE CreateDt > @CheckDate

Also- remember to call out your columns- don't use "SELECT *" in normal circumstances.

Upvotes: 2

paulsm4
paulsm4

Reputation: 121649

"Where createdate > (createdate - 2 weeks)"? I don't think so ;)

Try this:

... where createdate > dateadd (d, -15, getdate())

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453278

WHERE CreateDt > dateadd(d,-15,CreateDt)

should be 

WHERE CreateDt > dateadd(d,-15,getdate())

Presumably.

All NOT NULL values of CreateDt will meet your current condition as you are comparing the column with its own value minus 15 days - not 15 days previous to the current date and time.

Upvotes: 9

Related Questions