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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.