CODEWITHSUNDEEP
sql-server
Andy
Andy

Reputation: 667

How to compare datetime in SQL Server in where clause

I have CreatedDate as datetime column in my database table. I want to fetch the rows where CreatedDate and current time difference is more than 1 hour

Upvotes: 0

Views: 7484

Answers (6)

Suraj Singh
Suraj Singh

Reputation: 4059

   CREATE TABLE trialforDate
    (
      id INT NULL,
      NAME VARCHAR(20) NULL,
      addeddate DATETIME NULL
    )
INSERT INTO trialforDate VALUES (1,'xxxx',GETDATE())
INSERT INTO trialforDate VALUES (2,'yyyy',GETDATE())
INSERT INTO trialforDate VALUES (1,'zzzz','2013-09-12 11:20:40.533')

    SELECT  *
    FROM    trialforDate
    WHERE   GETDATE() >  DATEADD(HOUR, 1, addeddate)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

If you don't expect any rows created in the future...

where CreatedDate < dateadd(hour, -1, getdate())

Upvotes: 0

Bala
Bala

Reputation: 648

C# Code

DateTime param1= System.DateTime.Now;
DateTime param2= System.DateTime.Now.AddHours(1);

SQL Query:

SELECT * FROM TableName WHERE CreatedDate = param1 AND CreatedDate =param2;

Upvotes: -1

Jan Van Herck
Jan Van Herck

Reputation: 2284

Dan Bellandi raises a valid point, but if it really matters if the dates should be 60 minutes apart, then just check if they are 60 minutes apart:

SELECT * FROM TableName WHERE DATEDIFF(MINUTE, DateColumnName, GETDATE()) >= 60

Upvotes: 1

Dan Bellandi
Dan Bellandi

Reputation: 546

Answer by @Amit Singh works if you only care about the hour value itself, versus any 60 minute period.

The problem with using DATEDIFF(hh) that way is that times of 13:01 and 14:59 are only one "hour" apart.

Like:

select datediff(hh,'1/1/2001 13:59','1/1/2001 14:01')

I think doing this would address that issue:

declare @cd datetime='9/12/2013 03:10';

declare @t table(id int,CreatedDate datetime);
insert @t select 1,'9/12/2013 02:50';
insert @t select 2,'9/12/2013 02:05';


select * from @t where @cd>(DateAdd(hh,1,CreatedDate))

Upvotes: 1

Amit Singh
Amit Singh

Reputation: 8109

Select * from TableName where (DateDiff(hh,CreatedDate,GetDate())>1

Upvotes: 1

Related Questions