CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008datediff
Jason
Jason

Reputation: 17956

DateDiff Rounding in SQL Server

I have a table that contains the last date/time a particular function was ran. In my stored procedure, I want to return 1 if the current date/time is more than an hour away from the last time it was ran and 0 if it was less. Currently I have this:

IF((SELECT TOP 1 DATEDIFF(HH,LastRunDateTime,GETDATE()) FROM myTable) >= 1)
BEGIN
    -- run statement to update LastRunDateTime
        return 1;
END
ELSE
    return 0;
END

How does rounding work with DateDiff?

Is there a better way to only return 1 if it has been more than an hour?

Upvotes: 3

Views: 5162

Answers (2)

Bounderby
Bounderby

Reputation: 525

I think you want

IF DATEADD(hour, LastRunDateTime, 1) <= GETDATE() BEGIN
    RETURN 1;
END;
ELSE BEGIN
    RETURN 0;
END;

DateDiff is a little more subtle than just subtracting two datetimes. It actually tells you how many "boundaries" are crossed between the two. For example:

PRINT DATEDIFF(HH, '2010-12-07T03:59:59', '2010-12-07T04:00:00');
PRINT DATEDIFF(HH, '2010-12-07T04:00:00', '2010-12-07T04:59:59');

prints the following:

1
0

which is confusing, because the second pair of datetimes are farther apart. This functionality is great when you need it, but counterintuitive when you don't.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332531

Use:

SELECT CASE 
         WHEN DATEDIFF(hh, LastRunDateTime, GETDATE()) >= 1 THEN 1 
         ELSE 0 
       END

Reference:

How does rounding work with DateDiff?

The datepart Boundaries section in the documentation link provided lists such information.

Upvotes: 2

Related Questions