All Forums Database
ysrinu 44 posts Joined 04/07
10 Jul 2012
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

CarlosAL 512 posts Joined 04/08
11 Jul 2012

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.

You must sign in to leave a comment.