SQL Server日期时间问题 [英] SQL Server datetime problem

问题描述

以下查询显示没有数据，但数据在数据库中可用。在数据库中，数据类型是datetime。数据库字段值是2015-07-09 12:17:00。提前谢谢。

The following query shows no data but data is available in database. In database the datatype is datetime. The database field value is 2015-07-09 12:17:00.Thanks in advance.

SELECT     Smt_GoodsIssue.nCode, Smt_GoodsIssue.nIssNo, Smt_GoodsIssue.nItemcode, Smt_GoodsIssue.nIssQty, Smt_ItemMaster.cItemDes, Smt_ItemMaster.nItemBalQty, 
                      Smt_Sizes.cSize1, Smt_Dimension.cDimen, Smt_GoodsIssue.cUnit
FROM         Smt_GoodsIssue INNER JOIN
                      Smt_ItemMaster ON Smt_GoodsIssue.nItemcode = Smt_ItemMaster.nItemCode INNER JOIN
                      Smt_Sizes ON Smt_ItemMaster.nsize = Smt_Sizes.nCode INNER JOIN
                      Smt_Dimension ON Smt_ItemMaster.nDimec = Smt_Dimension.ndCode
where (Smt_GoodsIssue.dEntDAte between '@FromDate' and '@ToDate')
order by Smt_GoodsIssue.dEntDAte

推荐答案

嗯。

Um.

where (Smt_GoodsIssue.dEntDAte between '09-jul-2015' and '09-Jul-2015')

所以，什么时候SQL会将您提供的日期解析为DATETIME值，您认为它将在什么时间使用？

简而言之，2015年7月9日将被解释为2015-07-09 00：00：00.0000，因此只有在午夜准确输入数据库列数据时才会匹配。您的示例值是12小时17分钟后，因此它超出了匹配范围。



我建议这可能是更好的方法：

So, when SQL parses the dates you give it into DATETIME values, what time of day do you think it will use?
Simply put, 09-Jul-2015 will be interpreted as 2015-07-09 00:00:00.0000 so the database column data will only ever match if it is entered precisely at midnight. Your example value is twelve hours and 17 minutes later, so it falls outside the "match" range.

I'd suggest that this might be a better approach:

where (Smt_GoodsIssue.dEntDAte between '09-jul-2015' and '10-Jul-2015')

或者更好地使用DATEADD来抵消单个值，如果你实际上将它作为参数化值传递。

Or better use DATEADD to offset a single value if you are in reality passing it as a parameterised value.

你的日期时间字段包含日期和时间值。



cast日期部分的日期时间字段仅在where子句中。

your datetime field contains date & time value.

cast the datetime field for date part only in where clause.

这篇关于SQL Server日期时间问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！