在SQL Server 2005中使用日期范围 [英] Using Date Ranges in SQL Server 2005

问题描述

我在编写查询时遇到问题，想看看你们是否能帮上忙?我在两个不同的表中有两个具有相同名称的整数字段，我试图将这些字段求和以得到指定日期范围内的总容量，而我的查询返回的是wearg结果，但我无法找出问题所在.这是我正在使用的查询:
(日期是两个表中的主键)

I am having a problem with a query I am writing and would like to see if you guys could help out some? I have two integer fields with the same name in two different tables that I am trying to sum to get the total volume in a specified date range and my query is returning the worng results and I cannot figure out what is wrong. This is the query I am using:
(date is the primary key in both tables)

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A INNER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date
WHERE A.Date >='2012-09-09' and <'2012-09-15' AND B.Date >= '2012-09-09' and <'2012-09-15'AND A.DisplayAsID = 35 AND B.DisplayAsID = 35;

我也没有用到它:

I have also used this to no avail:

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A INNER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date
WHERE A.Date between '2012-09-09' and '2012-09-15' AND B.Date between '2012-09-09' and '2012-09-15'AND A.DisplayAsID = 35 AND B.DisplayAsID = 35;

而这个:

And this:

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A INNER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date
WHERE A.Date between '09/09/12' and '09/15/12' AND B.Date between '09/09/12' and '09/15/12' AND A.DisplayAsID = 35 AND B.DisplayAsID = 35;

任何帮助将不胜感激.这是我第一次发布问题，但经常将该站点用作参考:)

预先感谢，
BrianS.

Any help would be greatly appreciated.This is the first time I have posted a question but use the site often as a reference :)

Thanks in advance,
BrianS.

推荐答案

smalldatetime [ ^ ]字段包含的日期与一天中的某个时间结合在一起.时间基于一天的24小时制，秒始终为零(:00)，且不包含小数秒.请参见示例及其输出.

The smalldatetime[^] fields contain a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. See example and its output.

DECLARE @mydate smalldatetime

SET DATEFORMAT ymd;
SET @mydate = GETDATE()
SELECT @mydate as [CurrentDate]

输出:

Output:

[CurrentDate]
-------------------
2012-10-02 21:01:00

若要仅获取日期部分，请使用转换或CAST [

To get only date part, use CONVERT or CAST[^] function.

SELECT CONVERT(NVARCHAR(10), @mydate,121) as [CurrentDate]

输出:

Output:

[CurrentDate]
-------------------
2012-10-02

最后...要正确地联接日期字段上的表，您需要拒绝"时间部分...但是在执行此操作后，我不确定您的数据将是唯一的.

Finally... to correctly join tables on date field, you need to "reject" the time part... But after that operation i''m not sure that your data will be unique.

SELECT CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10), @mydate,121)) as [CurrentDate]

输出:

Output:

[CurrentDate]
-------------------
2012-10-02 00:00:00

我建议您创建临时表，将更正后的数据加载到该表中，然后选择...

I suggest you to create temporary table, load the corrected data into this table and select...

SET DATEFORMAT mdy;

CREATE TABLE #tt (TotalVol INT, MyDate SMALLDATETIME, ID INT)

INSERT INTO #tt (TotalVol, MyDate, ID)
SELECT [BOSS] AS [TotaVol], CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10),[Date],121) AS [MyDate], DisplayAsID As [ID]
FROM Keyed_Data AS KD LEFT JOIN Stored_Vol_Counts AS SVC ON CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10),KD.[Date],121)) = CONVERT(SMALLDATETIME, CONVERT(NVARCHAR(10),SVC.[Date],121))

DECLARE @dFrom SMALLDATETIME
DECLARE @dTo SMALLDATETIME

SET @dFrom = '09/09/12'
SET @dTo = '09/15/12'

--first view data
SELECT *
FROM #tt
WHERE (([MyDate] BETWEEN @dFrom AND @dTo) AND (ID = 35);

--get sum
SELECT SUM([TotalVol]) AS TotalVolInRange
FROM #tt
WHERE (([MyDate] BETWEEN @dFrom AND @dTo) AND (ID = 35);

DROP TABLE #tt

你们有提出了一些不错的选择和想法供我尝试，我非常感谢您为回答这些问题而付出的时间和精力.感谢大家的时间和贡献.但是，经过多次尝试和错误，我完成了一次简单的更改就使查询完美，这是通过将以下代码添加到查询的JOIN ON部分中以使查询对所选日期范围内的所有行求和来完成的.

You guys have came up with some good alternatives and ideas for me to try out and I really appreciate the time and effort that is put into answering these questions. Thanks everyone for their time and contributions. However, after much trial and error I have finished the query with one simple change that made it work perfect and that was by adding the following code to the JOIN ON part of the query to make the query sum all rows in the date range selected.

SELECT Sum(BOSS) AS TotalVolInRange
FROM Keyed_Data AS A FULL OUTER JOIN Stored_Vol_Counts AS B
ON A.Date = B.Date AND B.Date between ''+ @StartDate +'' AND ''+ @EndDate +'' AND B.DisplayAsID = @DisplayAsID AND B.ProductID = @Product
WHERE A.Date between ''+ @StartDate +'' AND '' + @EndDate +'' 
AND A.DisplayAsID = @DisplayAsID 
AND A.ProductID = @Product ;

这可以完美地完成工作，编写的代码更少，但是其他解决方案无疑是很好的有效解决方案.我们的SQL专家帮助我解决了这个问题，所以它不是我所有的人:)再次感谢您的帮助和想法.

BrianS.

This works perfect and was less code to write however the other solutions are definately good valid soulutions. Our SQL guru helped me out with this one so it was not all me:) Thank you again for the help and ideas.

BrianS.

这篇关于在SQL Server 2005中使用日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！