Searching for pairs of date/time values in a single row I have a legacy IBM DB2 database table that contains lots of records of phone calls.  It has columns for ID, customer number, employee who took the call, etc.  The date/time for each call is kept in a pair of columns, ROSDAT and ROSTIM.  ROSDAT is an 8 byte numeric column, and ROSTIM is a 6 byte numeric.  Example:

ROSDAT    ROSTIM  
20111006  163243  
20111007  012335


So for the first row the date is 2011-10-06, and the time is 16:32:43.  There are millions of records in this table, and I'm trying to make a query that will search it by time and date.  The only way I've found to do that so far is to concatenate the time/date columns into one big number like so:

select * from PHONELOGTABLE
where ROSDAT * power(10, 6) + ROSTIM &gt;= 20111015124500
and ROSDAT * power(10, 6) + ROSTIM &lt;= 20111116013000

This searches for everything from 2011-10-15 12:45:00 to 2012-11-16 01:30:00.  It works, but the concatenation means (as far as I know) that the indexes on the ROSDAT and ROSTIM columns cannot be used, so the query is slower than it should be.  It seems to me that there should be a way to do this with a join or something.

I tried this but couldn't get it to work, and I know I'm missing something:

select a.* from PHONELOGTABLE a join PHONELOGTABLE b on a.ID = b.ID
where a.ROSDAT &gt;= 20111015 and b.ROSTIM &gt;= 124500
and a.ROSDAT &lt;= 20111116 and b.ROSTIM &lt;= 013000

Anybody have an idea?
I'm not sure of db2 syntax, but how about this?

-- declare your 4 variables
@START_ROSDAT
@STOP_ROSDAT
@START_ROSTIM
@STOP_ROSTIM

select  *
from    PHONELOGTABLE
where   (   @start_rosdat &lt;&gt; @stop_rosdat                                    -- search covers more than 1 day
           and  (       (ROSDAT &gt; @START_ROSDAT AND ROSDAT &lt; @STOP_ROSDAT)   -- catches all full days between start and stop
                   or   (ROSDAT = @START_ROSDAT AND ROSTIM &gt;= @START_ROSTIM) -- catches everything on the "start" day
                   or   (ROSDAT = @STOP_ROSDAT AND ROSTIM &lt;= @STOP_ROSTIM)   -- catches everything on the "stop" day
                )
        )
  or    (   @start_rosdat = @stop_rosdat                                     -- only search a single "day"
           and  (       (ROSDAT = @START_ROSDAT AND ROSTIM &gt;= @START_ROSTIM)
                   and  (ROSDAT = @STOP_ROSDAT AND ROSTIM &lt;= @STOP_ROSTIM)
                )
        )




WHERE condition simplified a bit (the parentheses are there only for clarity, you can safely remove them):

where   ( ROSDAT &gt; @start_rosdat   and  ROSDAT &lt; @stop_rosdat )

   or   (  @start_rosdat &lt; @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM &gt;= @start_rostim )

   or   (  @start_rosdat &lt; @stop_rosdat 
      and  ROSDAT = @stop_rosdat   and  ROSTIM &lt;= @stop_rostim  )

   or   (  @start_rosdat = @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM &gt;= @start_rostim
                                   and  ROSTIM &lt;= @stop_rostim  )




and another version (parentheses are needed here):

where   (  ROSDAT &gt; @start_rosdat  
       or  ROSDAT = @start_rosdat  and  ROSTIM &gt;= @start_rostim )

   and  (  ROSDAT &lt; @stop_rosdat
       or  ROSDAT = @stop_rosdat   and  ROSTIM &lt;= @stop_rostim  )