How do I do a SQL BETWEEN where the date and time are stored seperatly as integers

At the company I work for date and time 开发者_如何学运维values have always been stored separately in integer fields, so for example 8:30 this morning would be stored like this:

• date of 20091116 and
• time of 83000 (no leading zeros as it is an integer field)

Whereas the time as I type this the time would be stored like this

• date 20091116
• time 133740

Unfortunately if i would like add a BETWEEN to the WHERE clause of a query it introduces a slight complication.

Currently the system I work on is using a query something like this:

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
    )

Bearing in mind that I can't change the design of the database...

Is there a better way to do this?

If you add them together, like:

 cast(20091116 as bigint) * 1000000 + 183000

You can do simpler math. For example:

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

Another way is to convert the two fields into a real datetime. You could do this with a computed column:

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)

If performance is an issue, you can use PERSISTED keyword to store the calculated columns on disk.

If you are concerned about performance (and thus have indices on txnDate and txnTime), you should use this:

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

Otherwise, Andomar's trick with bigint is fine and clear.

Redefine @minDate and @maxDate

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

Then perhaps the query can be simplified to

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

You should calculate the Date and Time components and make a single value that has DATETIME in it. You should do the same for the @variables. Then you do the comparison.

Doing this gives you several advantages such as data validation, datetime functions, accuracy, etc.

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)

Once you have your calculation defined, you can then create a view that sits on top of this table and use the view in any further calculations so that you do not have to retype the calculation every time.