All Forums Database
dixon 22 posts Joined 01/06
13 Jul 2006
operation on current_Timstamp

How to substract an hardcoded value from current_Timestamp. i am giving the following queries but it is throwing an error saying "5407 Invalid operation on ANSI Datetime or interval value"i tried with1. select CAST('2006-07-12 00:00:00' AS TIMESTAMP(0) with time zone FORMAT 'YYYY-MM-DDBHH:MI:SSBZ') - (CAST(cast(current_timestamp AS VARCHAR(19)) AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SSBZ'))2. select CAST('2006-07-12 00:00:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SSBZ') - (CAST(cast(current_timestamp AS VARCHAR(19)) AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SSBZ'))3. select (CAST('2006-07-12 00:00:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SSBZ')) - (CURRENT_TIMESTAMP(0) (FORMAT 'YYYY-MM-DDBHH:MI:SSBZ'))4. select (CAST('2006-07-12 00:00:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SSBZ')) - (CURRENT_TIMESTAMP(0)But in all these cases it is giving me the same error. is there anything i missed out here?can any one put some light on this.Thanks.

Dixon
vinod_sugur 22 posts Joined 04/05
14 Jul 2006

Hi,select ((CAST('2006-07-12 00:00:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SSBZ')) - (CURRENT_TIMESTAMP(0))) DAY(2) TO SECOND;We can't directly subtract two timestamp if we need difference we need use Interval as defined in above query: -1 23:55:39.000000Regards,

dixon 22 posts Joined 01/06
14 Jul 2006

Thanks vinod, it helped me a lot.

Dixon

You must sign in to leave a comment.