Search code examples
sqlsql-serverdatetimesql-server-2000

SELECT WHERE DATETIME is within 4 hours of another DATETIME SQL Server 2000

I have two tables with the datetime datatype, CreatedDate and CompletedDate and I want to find all the rows where the CompletedDate is within four hours of the CreatedDate.

My source database is SQL Server 2000 and it is linked to my SQL Server 2008. 

Created Date                Completed Date

2015-03-05 11:47:27.030     2015-03-06 17:32:58.107
Solution

  • Use dateadd()

    where completeddate <= dateadd(hour, 4, createddate)

    This should work in both versions of SQL Server.

    Note: You want to use dateadd() rather than datediff() to get exactly 4 hours. datediff() does not count the number of hours between two datetimes. It counts the number of "hour transitions" between two datetimes.

    Also, right after you do this, migrate your old database to a newer version of SQL Server. This should be an easy transition, and using supported software is highly recommended for any application.