SQL Server 2008 R2中的日期比较结果不正确 [英] Incorrect date comparison results in SQL Server 2008 R2
问题描述
DECLARE @ p0 DateTime ='2012-02-01 00：00：00.000'
DECLARE @ p1 DateTime ='2012-02-29 23：59：59.999'
....
WHERE（CONVERT（DATE，[t0]。[DatePlaced]）> = @ p0）AND（CONVERT（DATE，[t0]。[DatePlaced]）< = @ p1）
当这个运行时，我获得了3/1/2012的结果以及2012/2012的所有结果。 / p>
如果我更改where子句以使用 BETWEEN ，那么结果只包含2月的日期。
WHERE [t0]。[DatePlaced] BETWEEN @ p0和@ p1
我使用的是.net 4和SQL Server 2008 R2，带有和不带SP1。
将日期切换为3 / 1/2011，我查询的结束日期到'2011-02-28 23：59：59.999'产生相同的结果。
$ b $还有另一种方法除了使用LINQ 2 SQL不支持的BETWEEN之外，仅获得2/2012的结果？
.999圆到第二天的午夜。您可以查看：
DECLARE @ p1 DateTime ='2012-02-29 23：59：59.999';
SELECT @ p1;
你有什么？
试图找出今天的最后一刻（根据数据类型和精度会有所不同），你想要的是一个开放日期范围：
DECLARE @ p0 DATE ='2012-02-01'，
@ p1 DATE ='2012-03-01';
....
WHERE [t0]。[DatePlaced]> = @ p0
AND [t0]。[DatePlaced] @ p1
更容易的是在开始日期通过，并说：
DECLARE @ p0 DATE ='2012-02-01';
....
WHERE [t0] .DatePlaced> = @ p0
AND [t0] .DatePlaced< DATEADD（MONTH，1，@ p0）
有关datetime最佳做法的一些精心设计：
有关为什么 BETWEEN （以及扩展名> = AND < = ）是邪恶的：
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/什么与之间和恶魔之间的共同的.aspx
和Tibor的优秀的日期/时间资源：
http://www.karaszi.com/SQLServer/ info_datetime.asp
I have a LINQ 2 SQL query that's getting me a list of results for the month of February 2012. The resulting where clause is
DECLARE @p0 DateTime = '2012-02-01 00:00:00.000'
DECLARE @p1 DateTime = '2012-02-29 23:59:59.999'
....
WHERE (CONVERT(DATE, [t0].[DatePlaced]) >= @p0) AND (CONVERT(DATE, [t0].[DatePlaced]) <= @p1)
When this runs I'm getting results for 3/1/2012 showing up as well as all the results for 2/2012.
If I change the where clause to use BETWEEN then the results only contain dates for February.
WHERE [t0].[DatePlaced] BETWEEN @p0 AND @p1
I'm using .net 4 and SQL Server 2008 R2 with and without SP1.
Switching the dates to 3/1/2011 and my query's end date to '2011-02-28 23:59:59.999' yielded the same results.
Is there another way to get the results for just 2/2012 aside from using BETWEEN which LINQ 2 SQL doesn't support?
.999 rounds up to midnight of the next day. You can check this:
DECLARE @p1 DateTime = '2012-02-29 23:59:59.999';
SELECT @p1;
What do you get?
Instead of trying to figure out the last instant of today (which will be different depending on the data type and precision), what you want instead is an open-ended date range:
DECLARE @p0 DATE = '2012-02-01',
@p1 DATE = '2012-03-01';
....
WHERE [t0].[DatePlaced] >= @p0
AND [t0].[DatePlaced] < @p1
Even easier would be to just pass in the starting date and say:
DECLARE @p0 DATE = '2012-02-01';
....
WHERE [t0].DatePlaced >= @p0
AND [t0].DatePlaced < DATEADD(MONTH, 1, @p0)
For some elaborate ideas about datetime best practices:
For some info on why BETWEEN (and by extension >= AND <=) is evil:
And Tibor's excellent date/time resource:
http://www.karaszi.com/SQLServer/info_datetime.asp
这篇关于SQL Server 2008 R2中的日期比较结果不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
