Database
Highlighted
Enthusiast

## Whom can help me explain the ‘46.06:18:08’ time.

Dear SQLers,

A small question about time presentation using a integer data type. I juesed INTEGER FORMAT '99:99:99' to show time.

below in steps my chalance :-)

`SELECT CAST( 110025 AS INTEGER FORMAT '99:99:99') AS VALUE1;/*  WAARDE11 11:00:25*/SELECT CAST( 111033 AS INTEGER FORMAT '99:99:99') AS VALUE2;/*  WAARDE21 11:10:33*/SELECT CAST( 111033 AS INTEGER FORMAT '99:99:99') -       CAST( 110025 AS INTEGER FORMAT '99:99:99') AS VALUE3;/* WAARDE31    1008 --<<-- value not presentation in time, I expected; 00:10:08*//* My solution do an extra cast */SELECT CAST( ( CAST( 111033 AS INTEGER FORMAT '99:99:99')-               CAST( 110025 AS INTEGER FORMAT '99:99:99') ) AS INTEGER FORMAT '99:99:99') AS VALUE4;/*       WAARDE41 00:10:08 --<<-- OK !*//* I made a type mistake in my query and deduced the future time form the start time.   I expected a minus time; -00:10:08 but I was surprised by the result; 46.06:18:08 */SELECT CAST( ( CAST( 110025 AS INTEGER FORMAT '99:99:99')-               CAST( 111033 AS INTEGER FORMAT '99:99:99') ) AS INTEGER FORMAT '99:99:99') AS VALUE4;/*      VALUE41 46.06:18:08  --<<-- ??? I expected; -00:10:08*/`

Whom can help me explain the ‘46.06:18:08’ time.

Tags (2)
9 REPLIES 9

## Re: Whom can help me explain the ‘46.06:18:08’ time.

You are really substracting two integer numbers not two "time" columns.

So you are doing  111033- 1100025 and display the result in the formnmat '99:99:99'

You are not converting integer to time. Format clause is not doing a comnversion is just a way of displaying the result.

Format '99:99:99' is not a time format  what means is you want to display ":" between 2nd and 3rd digit and another one between 4th and 5th.

You can use format to intoduce any character you want , but does not mean any conversion

Format '99-99-99' will display 46-08-00

You should cast to time instead that cast to integer

Junior Supporter

## Re: Whom can help me explain the ‘46.06:18:08’ time.

I agree with you to some extent, But convertin this to time is not solving the issue, Can you please try the following queries:

`--Converting literals to timeSELECT CAST( '11:10:25'  AS TIME(0))  - CAST('11:00:33' AS TIME(0)) ;--Integer arithmaticSELECT 111033 - 110025 ;--Integer  arithmatic with formatSELECT CAST(111033 - 110025 AS INTEGER FORMAT '99:99:99') ;    --Integer arithmatic with time conversion             SELECT CAST( CAST( 110025 AS INTEGER FORMAT '99:99:99') AS TIME(0))-               CAST( CAST( 111033 AS INTEGER FORMAT '99:99:99') AS TIME(0))  AS VALUE4;`
Khurram
Junior Supporter

## Re: Whom can help me explain the ‘46.06:18:08’ time.

Hi,

as gerado has explained that even with a format the underlying data type do not change and the resulting arithmatic is simple INTEGER arithmatic not the Time arithmatic.

You can use the below query to find the time difference:

` SELECT TIME  '11:10:25'  - TIME '11:00:33' HOUR TO SECOND(0) ;`
Khurram

## Re: Whom can help me explain the ‘46.06:18:08’ time.

This strange value is only returned when you have an ODBC connection, so it seems to be a SQLA or ODBC bug:

`SELECT   CAST(-1008 AS SMALLINT FORMAT '99:99:99'),   CAST(-1008 AS INTEGER  FORMAT '99:99:99'),   CAST(-1008 AS FLOAT    FORMAT '99:99:99')-1,008  46.06:18:08 46.06:18:08`

You should open an incident.

As already noted don't try to calculate a time difference using INTs, use TIMEs instead.

Enthusiast

## Re: Whom can help me explain the ‘46.06:18:08’ time.

all SQL college's,

Thanks for the response, I have changed my procedure to make use of time instead of integer presented as time "lesson learned" THX. And Dieter, you’re right, I'm working from a windows client using SQLA wit which is connected by ODBC to the database.
Opening an incident is another chapter, it would be my first and at this moment i don't have a clue how to do this.

Joop
Junior Supporter

## Re: Whom can help me explain the ‘46.06:18:08’ time.

Hi,

There are several options available in ODBC for how to treat date and time, like III, AAA etc, I tried them as well but it didn't worked.

But its always good to use time and date data types for date and time manipulations. It saves a lot of unexpected results.

Khurram

## Re: Whom can help me explain the ‘46.06:18:08’ time.

when you wrote

SELECT CAST( '11:10:25' AS TIME(0)) - CAST('11:00:33' AS TIME(0))

you are missing HOUR TO SECOND

You should write

SELECT CAST( '11:10:25' AS TIME(0)) - CAST('11:00:33' AS TIME(0)) HOUR TO SECOND ;

Junior Supporter

## Re: Whom can help me explain the ‘46.06:18:08’ time.

This query is not working for me, Have you tested it on your machine?

Khurram

## Re: Whom can help me explain the ‘46.06:18:08’ time.

I have TD 14.00 and using SQL assistant 14.01

Running

SELECT CAST( '11:10:25' AS TIME(0)) - CAST('11:00:33' AS TIME(0)) HOUR TO SECOND ;