Issue while converting float to time and inserting to table

Database
Ambassador

Re: Issue while converting float to time and inserting to table

There's no implicit cast for TIME and TIMESTAMP, so you must do it explicitly:

SELECT 'aaa'||cast(A.RUNTIME as char(8))

Enthusiast

Re: Issue while converting float to time and inserting to table

Hi Dieter,

Thanks for your response.

I have tried it now but it gives me the error "Invalid operation for Date time or interval"

SELECT 'aaa'||cast(A.RUNTIME as char(8))
FROM
(
select
cast(trim(THETIME) AS time) AS RUNTIME
FROM DBC.RESUSAGESPMA
WHERE thedate = DATE and
cast(trim(THETIME) AS time) BETWEEN (current_time- interval '3' hour) AND current_time
GROUP BY THETIME
) A
Enthusiast

Re: Issue while converting float to time and inserting to table

Hi 

I could find the same working finde with 

SELECT 'aaa'||cast(current_time as char(8))

But with the below query having the 5407 error.

SELECT 'aaa'||cast(A.RUNTIME as char(8))
FROM
(
select
cast(trim(THETIME) AS time) AS RUNTIME
FROM DBC.RESUSAGESPMA
WHERE thedate = DATE and
cast(trim(THETIME) AS time) BETWEEN (current_time- interval '3' hour) AND current_time
GROUP BY THETIME
) A
Teradata Employee

Re: Issue while converting float to time and inserting to table

Are you using ODBC driver to submit the query? If so, is the middle letter of DateTimeFormat set to backward-compatible "I"? Generally you should set DateTimeFormat to either IAA or AAA.

But don't do range comparison with DATE and TIME separately. What if the range spans midnight? And if time zone handling is enabled on your system, that's midnight UTC (GMT) and not local time. Instead, combine date and time into a TIMESTAMP (and compare with CURRENT_TIMESTAMP).

CAST(CAST(CAST(TheDate AS FORMAT 'yyyy-mm-dd') AS CHAR(10))||' '||CAST(THETIME AS CHAR(8)) AS TIMESTAMP)

Highlighted
Enthusiast

Re: Issue while converting float to time and inserting to table

Hi Fred ,

Thanks a lot for your response.

Let me check with it , I suppose that the above fix is for SQLAw which connects through ODBC.

Can we know if it applies for BTEQ too ?

As im finiding the issue is with BTEQ

Regarding the second point , yes thats a very valid point , need to remodify the code.

Thanks for pointing out the same.

regards,

Subramanian kaushik Gurumoorthy

Teradata Employee

Re: Issue while converting float to time and inserting to table

The potential DateTimeFormat issue applies only to ODBC, not CLIv2 (which BTEQ uses) or Teradata.Net or JDBC.