AS400/DB2 - Converting integer based dates and times to a timestamp?

Question

I have 4 pieces of user input: startDate, endDate, startTime, endTime.

The dates and times in the database are setup differently than anything I have used before. The dates are the number of days since December 31st, 1900. So an integer. For example, October 16, 2015, would be represented as 41927.

The times are in ISO format - with no decimals, so also an integer. So 08:00:00 would be 80000. 19:00:00 would be 190000. It's basically hhmmss - but sometimes you only have one h.

I am trying to filter the dates and times into ranges. On one day where I'm just looking at times this works fine:

SELECT * FROM database WHERE day = 41927 AND time > startTime and time < endTime

It also works if you're strictly looking at days:

SELECT * FROM database WHERE day > startDate AND day < endDate

The problem comes in when you need to find items between two dates on two times because it will filter out anything not between those two times on every day. I know there is a way to create a timestamp, but I'm struggling to put it together within the query using two integers.

What kind of options do I have to tackle this?

Answer 1

You can use this with simple arithmetic:

select date('1900-12-31') + col days

However, I suspect the real date start date is 1899-12-31. That is the date used by Excel (so day 1 is 1900-01-01).

EDIT:

Oops, left out the seconds. One method is:

select timestamp(date('1900-12-31') + coldays days) +
       (floor(coltime / 10000) * 60*60 +
        mod(floor(coltime / 100)) * 60
        mod(coltime, 100)
       ) seconds