计算T-SQL中日期的差异

Guys, I am trying to write a stored procedure in T-SQL (SQL Server) that will select records based on a date field, keeping a variance of minutes in mind. Something like this:

伙计们,我正在尝试在T-SQL(SQL Server)中编写一个存储过程,它将根据日期字段选择记录,并记住几分钟的差异。像这样的东西:

CREATE PROCEDURE spGetCustomers(@DateRange DATETIME, @Variance int) AS
-- The next line is where I need help
-- I'm trying to subtract X amount of minutes from the date
-- So if @Variance = 4 AND @DateRange = '6/10/2009 1:15pm'
-- Then @StartDate should equal '6/10/2009 1:11pm'
DECLARE @StartDate = @DateRange - @Variance
-- I also need an @EndDate, which will be X amount of minutes
-- in the future. So if @Variance = 4 AND @DateRange = '6/10/2009 1:15pm'
-- Then @EndDate should equal '6/10/2009 1:19pm'
DECLARE @EndDate = @DateRange + @Variance

SELECT * FROM Customers WHERE Created BETWEEN @StartDate AND @EndDate

Hopefully this makes sense and someone can help me out! Thanks in advance

希望这是有道理的,有人可以帮助我!提前致谢

2 个解决方案

#1

Check this out:

看一下这个:

http://msdn.microsoft.com/en-us/library/ms186819(SQL.90).aspx

The DATEADD function allows you to add virtually any part of a date to another date object, it should be everything you need.

DATEADD函数允许您将日期的几乎任何部分添加到另一个日期对象,它应该是您需要的一切。

So basically do:

基本上这样做:

SELECT DATEADD(second, @Variance, @DateRange)

#2

The following script provides an example that should get you started.

以下脚本提供了一个可以帮助您入门的示例。

create table tmp_Customers
(
    ID int identity(1,1),
    CreatedDate datetime default getDate() not null,
    Description varchar(15)
);
go

insert into tmp_Customers(Description) values('SomeData');
insert into tmp_Customers(Description) values('SomeData2');
insert into tmp_Customers(Description) values('SomeData3');
go

create procedure usp_GetCustomers

    @iVarianceMinutes   int,
    @iDateRange         datetime

as

    set nocount on

    declare @startDate  datetime
    declare @endDate    datetime

    --Define the date ranges for the select query
    set @startDate = dateAdd(minute,-@iVarianceMinutes,@iDateRange)
    set @endDate = dateAdd(minute,@iVarianceMinutes,@iDateRange)

    --Get the Customers that were created within this time range.
    SELECT * 
    FROM tmp_Customers 
    WHERE CreatedDate >= @startDate and CreatedDate < @endDate 


return(0);
go


--Execute the procedure
declare @testDate datetime;
set @testDate = getDate();

exec usp_GetCustomers 5,@testDate 

--drop procedure usp_GetCustomers
--drop table tmp_Customers

#1