Relative Date Range in T-SQL

Question

I am trying to select for all elements where createDt is in the last two weeks. I tried this code but it does not work.

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

Can someone tell me the correct way to do this?

Answer 1

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

it should be

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

Presumably.

All NOT NULL CreateDt values ​​will match your current condition when you compare a column with your value minus 15 days - not 15 days before the current date and time.

Answer 2

"Where created> (created - 2 weeks)"? I do not think so;)

Try the following:

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

Answer 3

You check if CreateDt is more than 15 days before itself. I guess you get a lot more records than you expect.

I would do something like this (for readability)

 Declare @CheckDate DateTime Set @CheckDate = dateadd(d, -15, GetDate()) SELECT [Columns] from dbo.mytable WHERE CreateDt > @CheckDate 

Also remember to call your columns - do not use "SELECT *" under normal conditions.

Answer 4

I believe that this is what you want.

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