如何在SQL Server之间排除上限 [英] How can I exclude upper limit in BETWEEN sql server

问题描述

我正在使用SQL Server作为数据库.我正在搜索输入的日期的行.这意味着搜索submission_date恰好是'12/13/2011'的行.首先，我将搜索条件日期转换为毫秒

I am using SQL Server as my database. I am searching for a row for the date that I have entered. This means searching rows where submission_date is exactly '12/13/2011'. First I am converting the search criteria date to milliseconds

即

Dec 13 2011 00:00:00 ='1323727200000'
Dec 14 2011 00:00:00 ='1323813600000'`

SELECT * 
FROM log_file 
WHERE submission_date BETWEEN '1323727200000' AND '1323813600000' 

此查询将搜索Dec 13 Midnight到Dec 14 Midnight，但是我想跳过上限值，即从Dec 13 2011 00:00:00到Dec 13 2011 59:59:59.为此，我认为使用>= and <.这是正确的方法吗?

This query will search for Dec 13 Midnight to Dec 14 Midnight, but I want to skip the upper limit value i.e. from Dec 13 2011 00:00:00 to Dec 13 2011 59:59:59. For this I thought to use >= and <. Is this a right approach?

完成此操作后，我有一个iBatis xml，我在其中编写以下内容，这给了我错误.

Having done this, I have a iBatis xml where I am writing the following which is giving me error.

<isNotEmpty prepend="AND" property="submissiondate">
   submission_date <![CDATA[ >= ]]> #submissiondate # AND <![CDATA[ < ]]> #submissiondate #
</isNotEmpty>

请提出这是否正确的方法.

Please suggest if this is the right approach.

谢谢

推荐答案

是的，通常将>=和<用于时间/日期范围查询

Yes, you'd use >= and < typically for time/date range queries

或者，您可以从上限减去3毫秒以获得当天(xxx 23:59.59.997)的最高datetime(不是较新的datetime2)值

Alternatively, you could subtract 3 milliseconds from the upper limit to get the highest datetime (not newer datetime2) value for that day (xxx 23:59.59.997)

SELECT * FROM   log_file
WHERE  submission_date BETWEEN 1323714600000 AND 1323801000000-3

注意:如果所有时间均为毫秒，则减去1可能就可以了...

Note: subtracting 1 would probably be OK if everything is milliseconds...

编辑，为什么3ms的示例

Edit, example of why 3ms

SELECT 
    DATEADD(millisecond, -1, '20111214'), -- 2011-12-14 00:00:00.000
    DATEADD(millisecond, -2, '20111214'), -- 2011-12-13 23:59:59.997
    DATEADD(millisecond, -3, '20111214')  -- 2011-12-13 23:59:59.997

有趣的是，您确定这是午夜吗?
1323813600秒，我得到2011-12-13 22:00:00

And interestingly, are you sure this is midnight?
For 1323813600 seconds, I get 2011-12-13 22:00:00

在SQL Server上:

On SQL Server:

SELECT DATEADD(second, 1323813600, '19700101') 

在MySQL上

SELECT FROM_UNIXTIME(1323813600)

这篇关于如何在SQL Server之间排除上限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！