为什么ss轮到最接近的分钟，会改变一天 [英] why ss round to nearest minute that will change day

问题描述

我正在使用sql server 2008 R2，由于一个问题，我可以知道smalldatetime轮到最接近的分钟。这里是MSDN的内容。

ss是二位数，范围从00到59，代表第二位。 29.998秒或更少的值向下舍入到最接近的分钟，29.999秒或更大的值将被舍入到最接近的分钟。

现在我面临一个问题，因为这个，我的参数类型在我的存储过程中是smalldatetime当我通过'2014-03 -23 23:59:59'其转换为日期 2014-03-24 00:00:00

我发现我应该将参数从 smalldatetime 转换为 nvarchar（30）

并解决问题。

但我真正的问题是为什么 smalldatetime 的行为 / code> 改变一天？

您可以尝试以下查询

  DECLARE @EndDate smallDatetime 
 SET @EndDate ='2014-03-23 23:59:59'
 SELECT @EndDate 
 
 DECLARE @EndDate smallDatetime 
 SET @EndDate ='2014- 03-23 23:59:30'
 SELECT @EndDate 
 
 DECLARE @EndDate smallDatetime 
 SET @EndDate ='2014-03-23 23:59:29'
 SELECT @EndDate 
  

解决方案

闹钟在我的在任何时候我都会看到有人在SQL中使用 23:59:59 。

这几乎总是因为他们想要通过执行以下操作，在查询中返回一整天的数据：

  WHERE date_field BETWEEN'2014-03-19 00 ：00：00'AND'2014-03-19 23:59:59'
  

这是因为 BETWEEN 包含边界值，您可能不希望包含 2014-03-20 00:00:00 中的值。

ve查询相当于：

  WHERE date_field> ='2014-03-19 00:00:00'
 AND date_field< ='2014-03-19 23:59:59'
  

正确的方法

  WHERE date_field> ='2014-03-19 00:00:00'
 AND date_field< '2014-03-20 00:00:00'
  

注意运算符的细微差别。 / p>

I am using sql server 2008 R2 and due to one problem i am able to know that smalldatetime round ss to nearest minute . here is content from MSDN .

ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.

Now i face one problem due to this, that i have parameter type in my storedproceduer is smalldatetime when i pass '2014-03-23 23:59:59' its converted to date 2014-03-24 00:00:00

I found the solution that i should convert parameters from smalldatetime to nvarchar(30)

and problem solved.

but my real question why such behavior of smalldatetime rounding of ss that change the day ?

you can try it with following queries

DECLARE @EndDate smallDatetime
SET @EndDate = '2014-03-23 23:59:59'
SELECT @EndDate

DECLARE @EndDate smallDatetime
SET @EndDate = '2014-03-23 23:59:30'
SELECT @EndDate

DECLARE @EndDate smallDatetime
SET @EndDate = '2014-03-23 23:59:29'
SELECT @EndDate

解决方案

Alarm bells go off in my head any time I see someone using 23:59:59 in SQL.

This is almost always because they want to return a full days worth of data in the query by doing something like:

WHERE  date_field BETWEEN '2014-03-19 00:00:00' AND '2014-03-19 23:59:59'

This is because BETWEEN includes the boundary values and you might not want values at 2014-03-20 00:00:00 included.

The above query is equivalent to:

WHERE  date_field >= '2014-03-19 00:00:00'
AND    date_field <= '2014-03-19 23:59:59'

The Correct Approach

WHERE  date_field >= '2014-03-19 00:00:00'
AND    date_field <  '2014-03-20 00:00:00'

Notice the subtle difference in the operators.

这篇关于为什么ss轮到最接近的分钟，会改变一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！