如何在日期和时间作为整数单独存储的情况下执行 SQL BETWEEN

Question

在我工作的公司，日期和时间值始终单独存储在整数字段中，因此例如今天早上 8:30 将存储如下：

• 日期 20091116 和
• 时间 83000（没有前导零，因为它是整数字段）

而当我输入这个时间时，时间将像这样存储：

• 日期 20091116
• 时间 133740

不幸的是，如果我想将 BETWEEN 添加到查询的 WHERE 子句中，它会带来轻微的复杂性。

目前我工作的系统正在使用类似这样的查询：

declare @minDate int, @minTime int, @maxDate int, @maxTime int
select @minDate = 20091102
select @minTime = 64841
select @maxDate = 20091105
select @maxTime = 102227

SELECT *
FROM transactions
WHERE
    (
        (
            txnDate = @minDate AND 
            txnTime >= @minTime 
        ) OR 
        txnDate > @minDate
    ) AND
    (
        (
            txnDate = @maxDate AND
            txnTime <= @maxTime
        ) OR
        txnDate < @maxDate
    )

请记住我无法更改数据库的设计...
有更好的方法吗？

Answer 1

如果将它们加在一起，例如：

 cast(20091116 as bigint) * 1000000 + 183000

您可以做更简单的数学。例如：

select @minDate = 20091102064841
select @maxDate = 20091105102227

select *
from (
    select cast(txnDate as bigint) * 1000000 + 
        txnTime as composite_date,
        *
    from YourTable
) sub
where composite_date between @minDate and @maxDate

另一种方法是将这两个字段转换为真正的日期时间。您可以使用计算列来执行此操作：

alter table YourTable add txnDateTime as cast(
    cast(txnDate as varchar) + ' ' + 
    cast(txnTime / 10000 as varchar) + ':' +
    cast(txnTime / 100 % 100 as varchar) + ':' +
    cast(txnTime % 100 as varchar)
 as datetime)

如果性能是一个问题，您可以使用 PERSISTED< /a> 关键字将计算的列存储在磁盘上。

Answer 2

如果您担心性能（因此在 txnDate 和 txnTime 上有索引），您应该使用这个：

SELECT * FROM transactions
WHERE (txnDate > @minDate AND txnDate < @maxDate)
   OR (txnDate = @minDate AND txnTime >= @minTime)
   OR (txnDate = @maxDate AND txnTime <= @maxTime)

否则，Andomar 的 bigint 技巧很好且清晰。

Answer 3

重新定义@minDate和@maxDate

declare @minDate bigint, @maxDate bigint
select @minDate = 20091102064841
select @maxDate = 20091105102227

那么也许查询可以简化为

SELECT *
FROM transactions
WHERE ((CAST(txnDate AS bigint) * 1000000) + txnTime) BETWEEN @minDate AND @maxDate

Answer 4

您应该计算日期和时间组件并创建一个包含 DATETIME 的值。您应该对@variables 执行相同的操作。然后你再做比较。

这样做可以给您带来多种优势，例如数据验证、日期时间函数、准确性等。

declare @minDate int, @minTime int, @maxDate int, @maxTime int
select @minDate = 20091102
select @minTime = 64841
select @maxDate = 20091105
select @maxTime = 102227

DECLARE @MinDateTime DateTime

select @MinDateTime =
    cast (convert (varchar, @minDate / 10000) + '/' +
    convert (varchar, (@minDate % (@minDate / 10000))/100) + '/' +
    convert (varchar, (@minDate % (@minDate / 1000000))) + ' ' +
    convert (varchar, @minTime / 10000) + ':' +
    convert (varchar, (@minTime % 10000)/100) + ':' +
    convert (varchar, (@minTime  % (@minTime / 100))) as datetime)

定义计算后，您可以创建一个位于此表顶部的视图，并在任何进一步的计算中使用该视图，以便您可以不必每次都重新输入计算。