Archives of the TeradataForum

Message Posted: Wed, 26 Mar 2003 @ 20:32:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

1. Begin_time and end_time are no Teradata Timestamps, it's just a char(16).

2. Minute() is no Teradata SQL, it's an ODBC funtion and only works within Queryman:

If you uncheck "Allow use of ODBC SQL extensions in queries" in the Query-tab of the options-dialog (Tools->Options), you'll get an error message for this query.

The ODBC driver is parsing your statement and replacing the minute() function:

If you submit

SELECT begin_time, end_time, minute(end_time - begin_time) FROM process_log;

the following statement will be sent to Teradata:

SELECT begin_time , end_time ,
(((end_time - begin_time )(INTEGER))/100 MOD 100(TITLE 'Minute()'))
FROM process_log;

You have to cast the column to a Timestamp before doing TS-calculations. A cast(begin_time as timestamo(0)) returns an error message, because the format is wrong.

If you're running V2R5 you can use

cast(end_time as timestamp(0) format 'YY/MM/DDHH:MI:SS')
else
cast('20' || substring (begin_time from 1 for 8) || ' ' ||
              substring (begin_time from 9) as timestamp(0))

The maximum number of digits in an interval is 4, so more than 02:46:39 can't be represented as seconds and 6 days 22:39:00 is the maximum for minutes.

And you can't use a case to return different intervals in one column, so why don't you use interval minute to second?

SELECT begin_time, end_time,
   (cast(end_time as timestamp(0) format 'YY/MM/DDHH:MI:SS') -
    cast(begin_time as timestamp(0) format 'YY/MM/DDHH:MI:SS'))
minute(4) to second(0) as elapsed
FROM  process_log;

begin_time        end_time          elapsed
----------------  ----------------  -------
03/03/1815:00:17  03/03/1815:00:21     0:04
03/03/1714:59:31  03/03/1715:26:10    26:39
03/03/1917:10:40  03/03/1917:11:16     0:36
03/03/2609:53:28  03/03/2610:14:22    20:54
03/03/1911:05:38  03/03/1911:06:22     0:44
03/03/2110:24:16  03/03/2110:45:19    21:03
03/03/2610:42:40  ?                       ?
03/03/1917:16:21  03/03/1917:17:04     0:43
03/03/1813:04:30  03/03/1813:11:27     6:57
03/03/2610:17:10  03/03/2610:38:53    21:43

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>