哪一个:
日期时间日期时间2
在SQL Server 2008+中存储日期和时间的建议方法是什么?
我知道精度(可能还有存储空间)的差异,但现在忽略这些,是否有关于何时使用what的最佳实践文档,或者我们应该只使用datetime2?
哪一个:
日期时间日期时间2
在SQL Server 2008+中存储日期和时间的建议方法是什么?
我知道精度(可能还有存储空间)的差异,但现在忽略这些,是否有关于何时使用what的最佳实践文档,或者我们应该只使用datetime2?
当前回答
使用非US DATEFORMAT设置时,将日期字符串解释为datetime和datetime2也可能不同。例如。
set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2
对于datetime,返回2013-05-06(即5月6日),对于datetime2,返回2013-06-05(即6月5日)。然而,当dateformat设置为mdy时,@d和@d2都返回2013-06-05。
日期时间行为似乎与MSDN的SETDATEFORMAT文档不一致,后者指出:某些字符串格式(例如ISO8601)的解释与DATEFORMAT设置无关。显然不是真的!
直到我被这个问题困扰,我一直认为yyyy-mm-dd日期会被正确处理,不管语言/语言环境设置如何。
其他回答
虽然datetime2提高了精度,但有些客户端不支持date、time或datetime2,并强制您转换为字符串文本。微软特别提到了ODBC、OLE DB、JDBC和SqlClient这些数据类型的“底层”问题,并有一个图表显示了它们如何映射类型。
如果值兼容性超过精度,请使用datetime
我同意@marc_s和@Adam_Poward的观点——DateTime2是前进的首选方法。它的日期范围更广,精度更高,使用的存储量相等或更少(取决于精度)。
然而,讨论中遗漏了一件事。。。@Marc_s状态:这两种类型都映射到.NET中的System.DateTime-没有区别。这是正确的,然而,相反的是不正确的。。。在进行日期范围搜索时很重要(例如“查找2010年5月5日修改的所有记录”)。
.NET版本的Datetime具有与DateTime2相似的范围和精度。将.net Datetime向下映射到旧的SQL Datetime时,会发生隐式舍入。旧的SQL DateTime精确到3毫秒。这意味着11:59:59.997将尽可能接近一天的结束。任何更高的值都将舍入到第二天。
试试看:
declare @d1 datetime = '5/5/2010 23:59:59.999'
declare @d2 datetime2 = '5/5/2010 23:59:59.999'
declare @d3 datetime = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'
避免这种隐式舍入是转移到DateTime2的一个重要原因。日期的隐式舍入显然会引起混淆:
SQL Server中奇怪的日期时间行为http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-aSQL Server 2008和毫秒http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspxhttp://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx公司
正如其他答案所示,由于尺寸更小、精度更高,建议使用datetime2,但以下是Nikola Ilic关于为什么不使用datetime1的一些想法:
缺少(简单)对日期进行基本数学运算的可能性,如GETDATE()+1每次使用DATEADD或DATEDIFF进行比较时,都会完成到datetime的隐式数据转换SQL Server无法正确使用Datetime2列的统计信息,因为存储数据的方式会导致非最佳查询计划,从而降低性能
下面的示例将向您展示smalldatetime、datetime、datetime2(0)和datetime2(7)之间存储大小(字节)和精度的差异:
DECLARE @temp TABLE (
sdt smalldatetime,
dt datetime,
dt20 datetime2(0),
dt27 datetime2(7)
)
INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()
SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
dt,DATALENGTH(dt) as dt_bytes,
dt20,DATALENGTH(dt20) as dt20_bytes,
dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp
返回
sdt sdt_bytes dt dt_bytes dt20 dt20_bytes dt27 dt27_bytes
------------------- --------- ----------------------- -------- ------------------- ---------- --------------------------- ----------
2015-09-11 11:26:00 4 2015-09-11 11:25:42.417 8 2015-09-11 11:25:42 6 2015-09-11 11:25:42.4170000 8
因此,如果我想将信息存储到秒,而不是毫秒,那么如果我使用datetime2(0)而不是datetime或datetime 2(7),我可以每个存储2个字节。
接受的答案很好,只需知道,如果您向前端发送DateTime2,它将四舍五入到正常的DateTime等效值。
这给我带来了一个问题,因为在我的解决方案中,我必须在重新提交时将发送的内容与数据库中的内容进行比较,而我的简单比较“==”不允许舍入。所以必须添加它。