CODEWITHSUNDEEP
sql-server-2008datetimestored-procedures
7heViking
7heViking

Reputation: 7577

Compare datetime in stored procedure

I have a sql 2008 database and I am creating a stored procedure that shall check if a datetime is more than 3 hours old but I don't know how to do it.

Do you have some way to do it?

the datetime is a field in the table.

BR

Upvotes: 0

Views: 6605

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280413

Rather than applying DATEDIFF to the column value, which will negate an index, I suggest using a comparison of the column to an expression (which can use an index).

If you want this as a filter:

SELECT columns
FROM dbo.table
WHERE DateTimeColumn < DATEADD(HOUR, -3, CURRENT_TIMESTAMP);

(If you want only the rows that are newer than 3 hours old, change < to > or >=.)

If you want to return all rows with a column showing whether it is more than 3 hours old:

SELECT columns, [3HoursOld] = CASE
    WHEN DateTimeColumn < DATEADD(HOUR, -3, CURRENT_TIMESTAMP)
    THEN 'Yes, older than 3 hours.'
    ELSE 'No, not older than 3 hours.'
END
FROM dbo.table;

Upvotes: 2

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

@date is the date you want to compare

declare @date datetime

set @date= '2012-02-15 14:20:42.797' 

SELECT DateDiff(hh, DATEADD(hh,-3,@date), GETDATE()) --if it's > 3

you better create a Boolean function that does the trick that you can use where ever you like

Upvotes: 1

Dervall
Dervall

Reputation: 5744

Take at look at the DATEDIFF function.

DATEDIFF ( datepart , startdate , enddate )

You would then use with datepart set to hh and the enddate set to the current time. To get the current database time you could use GETDATE(). Compare the result with 3 since it will return the number of hours passed.

Upvotes: 1

Related Questions