CODEWITHSUNDEEP
t-sqlsql-server-2008
George2
George2

Reputation: 45761

SQL DateTime type issue

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

Upvotes: 0

Views: 91

Answers (5)

codingbadger
codingbadger

Reputation: 43974

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

Upvotes: 1

veljkoz
veljkoz

Reputation: 8514

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

Upvotes: 3

HCL
HCL

Reputation: 36765

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

Upvotes: 3

halfdan
halfdan

Reputation: 34204

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

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

Upvotes: 1

Christian Watteng&#229;rd
Christian Watteng&#229;rd

Reputation: 5681

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

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

Edit: It was the other way around :P

Upvotes: 1

Related Questions