All Forums Database
joop_kpn 7 posts Joined 02/12
04 Dec 2013
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;
/*
  WAARDE1
1 11:00:25
*/

SELECT CAST( 111033 AS INTEGER FORMAT '99:99:99') AS VALUE2;
/*
  WAARDE2
1 11:10:33
*/

SELECT CAST( 111033 AS INTEGER FORMAT '99:99:99') -
       CAST( 110025 AS INTEGER FORMAT '99:99:99') AS VALUE3;
/*
 WAARDE3
1    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;
/*
       WAARDE4
1 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;
/*
      VALUE4
1 46.06:18:08  --<<-- ??? I expected; -00:10:08
*/

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

JTE
gerardo 13 posts Joined 10/09
04 Dec 2013

 
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
 

M.Saeed Khurram 544 posts Joined 09/12
04 Dec 2013

Hi gerado,
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 time
SELECT CAST( '11:10:25'  AS TIME(0))  - CAST('11:00:33' AS TIME(0)) ;

--Integer arithmatic
SELECT 111033 - 110025 ;

--Integer  arithmatic with format
SELECT 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

M.Saeed Khurram 544 posts Joined 09/12
04 Dec 2013

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

dnoeth 4628 posts Joined 11/04
04 Dec 2013

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.

Dieter

joop_kpn 7 posts Joined 02/12
04 Dec 2013

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

JTE

M.Saeed Khurram 544 posts Joined 09/12
04 Dec 2013

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

gerardo 13 posts Joined 10/09
05 Dec 2013

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 ;
 
 
 
 

M.Saeed Khurram 544 posts Joined 09/12
05 Dec 2013

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

Khurram

gerardo 13 posts Joined 10/09
09 Dec 2013

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 ;
 
I get  as answer :
0:09:52.000000
that is right .
 
I have tried also with BTEQ 13.10 and works fine.
 

You must sign in to leave a comment.