SQL DateTime type issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise. I have a table called Foo and it has a DateTime type column called MyTime. I want to find all records (rows) whose MyTime column value has been elapsed for more than 3 days (elapsed from current time). How to implement this?

thanks in advance, George

Answer 1

datediff(d,GetDate(),MyTime) > 3

select * from table
  where datediff(d,GetDate(),MyTime) > 3

Edit: It was the other way around :P

Answer 2

You can use DATEADD(datepart, number, date) to do the calculation:

SELECT * FROM Foo WHERE MyTime > DATEADD(dd, -3, MyTime)

Answer 3

WHERE myTime < DATEADD(dd,-3,getDate())

Answer 4

If you have a lot of rows you'd probably want to remove per-row-expressions, so first find out what date is it that you seek, and then just compare:

DECLARE @DateExpired DATETIME
SET @DateExpired = GETDATE() - 3
SELECT * 
FROM Foo
WHERE MyTime < @DateExpired

Answer 5

create table #foo
(
MyTime dateTime
)

Insert Into #foo
Values ('2010-09-15')
Insert Into #foo
Values ('2010-09-14')
Insert Into #foo
Values ('2010-09-13') 
Insert Into #foo
Values ('2010-09-12')


Select * FRom #foo
Where DateAdd(day, 3, MyTime) <= '2010-09-16'

drop table #foo