SQL Server 和日期 + 1 结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6719742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server and date + 1 result
提问by ahmd0
In case of the SQL Server. Say, if I have a table with [myDate] column that is of the type datetime, what would adding 1 to it mean in the following context?
在 SQL Server 的情况下。比如说，如果我有一个包含 [myDate] 列的表，该列是 datetime 类型，那么在以下上下文中向它添加 1 意味着什么？
SELECT *
FROM [myTable]
WHERE [myDate] + 1 > @someDate
回答by gbn
It adds one day (exactly 24 hours)
它增加了一天（正好 24 小时）
However, your query is more correct this way. The function or processing on the column usually invalidates index usage if there is one of that column
但是，您的查询以这种方式更正确。如果存在该列之一，则该列上的函数或处理通常会使索引使用无效
WHERE [myDate] > @someDate - 1
OR
或者
WHERE [myDate] > DATEADD(day, -1, @someDate)
回答by marc_s
Use the DATEADDfunction (see MSDN docs for DATEADDfor details):
使用该DATEADD函数（有关 DATEADD的详细信息，请参阅MSDN 文档）：
SELECT *
FROM dbo.myTable
WHERE DATEADD(D, 1, myDate) > @someDate
DATEADDallows you to add any of the usual date parts (day, month, year) and also time parts (hours, minutes, seconds) etc.
DATEADD允许您添加任何常用的日期部分（日、月、年）和时间部分（小时、分钟、秒）等。
回答by Steve Morgan
A DATETIME can be expressed as a decimal value. The integer part represents the date while the decimal value represents the time.
DATETIME 可以表示为十进制值。整数部分代表日期，小数部分代表时间。
So [DATETIME] + 1 means 'same time the next day'.
所以 [DATETIME] + 1 意味着“第二天的同一时间”。
DateAdd is safer, as it assumes nothing about the underlying implementation.
DateAdd 更安全，因为它对底层实现没有任何假设。
回答by Charles Bretana
It will add one day. This takes advantedge of the fact that SQL Server stores dates as two integers, one representing the number of days since day "0" - (1 jan 1900), and the second one which represents the number of ticks (about 3.33 ms) since midnight (for the time) *.
它会增加一天。这利用了这样一个事实，即 SQL Server 将日期存储为两个整数，一个表示自“0”天（1900 年 1 月 1 日）以来的天数，第二个表示自午夜以来的滴答数（约 3.33 毫秒） （时间）*。
To make your queries use an index... use this formula on the input filtering parameters first, or on the "other" side of the equal sign from the tables date time field, so that the query optimizer does not have to run the calculation on every datetime field in the table to determine which rows satisfy the filter predicate. This makes your search argument "SARG-able" (Search ARGument)
要使您的查询使用索引...首先在输入过滤参数上使用此公式，或者在表日期时间字段的等号的“另一”侧使用此公式，以便查询优化器不必运行计算在表中的每个日期时间字段上确定哪些行满足过滤谓词。这使您的搜索参数“支持 SARG”（搜索 ARGument）
Where MyDateTimeColumn > DateAdd(day,
datediff(day,0, @MydateParameter), 0) -- SARG-able
rather than
而不是
Where DateAdd(day, datediff(day,0,
MyDateTimeColumn ), 0) > MydateParameter -- Not SARG-able ?
In your case, this means use
在您的情况下，这意味着使用
Select *
FROM [myTable]
WHERE [myDate] > @someDate - 1
rather than
而不是
Select *
FROM [myTable]
WHERE [myDate] + 1 > @someDate
NOTE. Internally, the second integer (the time part) stores ticks. In a day there are 24 x 60 X 60 X 300 = 25,920,000 ticks (serendipitously just below the max value a 32 bit integer can hold). However, you do not need to worry about this when arithmetically modifying a datetime... When adding or subtracting values from datetimes you can treat the value as a fraction as though it was exactly equal to the fractional portion of a day, as though the complete datetime value was a floating point number consisting of an integer portion representing the date and the fractional portion representing the time). i.e.,
笔记。在内部，第二个整数（时间部分）存储报价。一天中有 24 x 60 X 60 X 300 = 25,920,000 个滴答声（偶然地低于 32 位整数可以容纳的最大值）。但是，在对日期时间进行算术修改时，您无需担心这一点...当从日期时间添加或减去值时，您可以将该值视为一个分数，就好像它完全等于一天的小数部分一样，就好像完整的日期时间值是一个浮点数，由代表日期的整数部分和代表时间的小数部分组成）。IE，
Declare @Dt DateTime Set @Dt = getdate()
Set @Dt = @Dt + 1.0/24 -- Adds one hour
Select @Dt
Set @Dt = @Dt - .25 -- Moves back 6 hours
Select @Dt
回答by DaveL
Simply adding a number to a DATE type does not work.
简单地向 DATE 类型添加一个数字是行不通的。
This works:
这有效：
SELECT CAST('2014-11-21 14:30' AS DATETIME) + 1
This does not:
这不会：
SELECT CAST('2014-11-21 14:30' AS DATE) + 1
This can be an issue when updating database drivers that may map a date type to DATETIME (because that's all there was in SQL2005), but change to use DATE.
在更新可能将日期类型映射到 DATETIME 的数据库驱动程序时，这可能是一个问题（因为 SQL2005 中只有这些），但更改为使用 DATE。
回答by Adam Porad
It should add 1 day (24 hours) to the date, adding 0.5 will had half a day (12 hours) and adding 2 will add days (48 hours), etc. But @Barry makes a good point in his comment. You could try it and find out.
它应该在日期上添加 1 天（24 小时），添加 0.5 将有半天（12 小时），添加 2 将添加天（48 小时）等。但 @Barry 在他的评论中提出了一个很好的观点。你可以试试看就知道了。
If you want to add other minutes, seconds, milliseconds, etc. You should use the DATEADDfunction that is built-in to SQL Server.
如果要添加其他分钟、秒、毫秒等，则应使用SQL Server 内置的DATEADD函数。
