COALESCE on timestamp(2) datatype column

Database
Enthusiast

COALESCE on timestamp(2) datatype column

Hi, i have a column defined:

END_TM TIMESTAMP(2)

On select I want to use CURRENT_TIMESTAMP(2) when END_TM is null

However, the following sql

select COALESCE(END_TM,current_timestamp(2))

throws error:

SELECT Failed. 3800:  Datatype Mismatch in THEN/ELSE expression.

Why?

Thanks,

-srinivas

1 REPLY
Junior Supporter

Re: COALESCE on timestamp(2) datatype column

Srinivas:

Type mismatch:

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT TYPE(CURRENT_TIMESTAMP(2));

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

Type(Current TimeStamp(2))

---------------------------------------

TIMESTAMP(2) WITH TIME ZONE

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT 1 WHERE TYPE(CAST('2012-07-11 00;00:00.00' AS TIMESTAMP(2)))=TYPE(CURRENT_TIMESTAMP(2));

 *** Query completed. No rows found.

 *** Total elapsed time was 1 second.

Cheers.

Carlos.