operation on current_Timstamp

Database
Enthusiast

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 with
1. 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.
2 REPLIES
Enthusiast

Re: operation on current_Timstamp

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.000000

Regards,

Enthusiast

Re: operation on current_Timstamp

Thanks vinod, it helped me a lot.