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.
我有两个带有datetime数据类型的表,CreatedDate和CompletedDate,我想找到CompletedDate在CreatedDate四小时内的所有行。
My source database is SQL Server 2000 and it is linked to my SQL Server 2008.
我的源数据库是SQL Server 2000,它链接到我的SQL Server 2008。
Created Date Completed Date
2015-03-05 11:47:27.030 2015-03-06 17:32:58.107
1 个解决方案
#1
0
Use dateadd()
where completeddate <= dateadd(hour, 4, createddate)
This should work in both versions of SQL Server.
这应该适用于两个版本的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.
注意:您希望使用dateadd()而不是datediff()来获得恰好4个小时。 datediff()不计算两个日期时间之间的小时数。它计算两个日期时间之间的“小时转换”的数量。
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.
此外,在执行此操作后,将旧数据库迁移到较新版本的SQL Server。这应该是一个简单的过渡,强烈建议使用支持的软件用于任何应用程序。
#1
0
Use dateadd()
where completeddate <= dateadd(hour, 4, createddate)
This should work in both versions of SQL Server.
这应该适用于两个版本的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.
注意:您希望使用dateadd()而不是datediff()来获得恰好4个小时。 datediff()不计算两个日期时间之间的小时数。它计算两个日期时间之间的“小时转换”的数量。
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.
此外,在执行此操作后,将旧数据库迁移到较新版本的SQL Server。这应该是一个简单的过渡,强烈建议使用支持的软件用于任何应用程序。