Subject Re: date non zero time substraction
Author Adam
--- In, Alejandro Garcia
<aleplgr@...> wrote:
> Hi! I'm working with a date difference :
> If DATE1 and DATE2 are dd/mm/aaaa hh:mm:ss with
> hh:mm:ss = 00:00:00 the dif value is an integer and works fine
> But when hh:mm:ss are non zero in DATE2 the dif value I'm
getting are with decimals and I need them to be as if the hh:mm:ss
were zero like in the first case, I can't just round the result
because it's obviously not the same.
> Is there a way to update just the hh:mm:ss value in the
TABLE2.DATE2 and make it 00:00:00? or how could I fix this?
> Thanks in advance

The Timestamp data type contains both a date and a time part. A date
(in dialect 3 anyway) contains the date component only. Subtracting
two timestamps will not return an integer, but a decimal number where
every 1 represents a 24 hour period. Of course if the times on those
days are equal and the dates were 5 days apart, it will be 5.0, but an
18 hour gap would be represented as 0.75

You wrote:

Presuming you have a dialect 3 database and that TABLE1.DATE1 and
TABLE2.DATE2 are Timestamp fields, you can cast these to a date field
before the subtraction. If you have a dialect 1 database, the date
datatype actually contains a time component, so I imagine a UDF
solution would be required. But in dialect 3, it would look like this.

dif= F_IBABS (cast(TABLE1.DATE1 as date)- cast(TABLE2.DATE2 as date));