0

Hi,

I am trying to update the timestamp into a table like this:

Update tablea set status = 'Succeeded', end_date = cast(cast(current_timestamp(0) as format 'dd/mm/y4bhh:mi:ss') as char(19)) where id = 3456;

I am getting Invalid timestamp for this query. When I check the timestamp data through select it looks like this:

select cast(cast(current_timestamp(0) as format 'dd/mm/y4bhh:mi:ss') as char(19))

Resultset:27/10/2009 02:56:12

I want to update the end_date through current_timestamp not by putting timestamp value in it.

Please help me in this. Thanks and Regards, TDHelp

flag

2 Answers

0

Try removing the second cast to CHAR(19) in your update statement. If you are inserting it as a char value it must match the format as defined on the column in the table. See below:

CREATE VOLATILE TABLE MyTable, NO LOG
  (
   MyID SMALLINT NOT NULL,
   EndDate TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS' NOT NULL
  )
ON COMMIT PRESERVE ROWS;

INSERT INTO MyTable VALUES (1, TIMESTAMP '27/10/2009 02:56:12'); /* 3706 Syntax Error: Invalid TimeStamp Literal' */
INSERT INTO MyTable VALUES (1, TIMESTAMP '2009-10-27 02:56:12'); /* Successful */
INSERT INTO MyTable VALUES (2, cast('27/10/2009 02:56:12' AS TIMESTAMP(0) FORMAT 'DD/MM/YYYYBHH:MI:SS')) /* Successful */

SELECT *
FROM MyTable
;

2   2009-10-27 02:56:12
1   2009-10-27 02:56:12
link|flag
0

You didn't post your CREATE TABLE statement but I think you defined end_date as a timestamp type column. If so, you don't need to cast current_timestamp(0) to CHAR. Just use:

 update tablea
   set status = 'Succeeded'
     , end_date = current_timestamp(0)
 where id = 3456;

The data in timestamp columns is not kept in any specific format. The format in the column definition is used as the default output format when converting the timestamp to char for output. If you want to output in that format, you can define the column like this:

create table tablea
(
  id integer not null,
  status char(20),
  end_date timestamp(0) format 'dd/mm/y4bhh:mi:ss'
) primary index(id);

insert into tablea values (3456, 'ok',current_timestamp(0));

update tablea
   set status = 'Succeeded'
        , end_date = current_timestamp(0)
where id = 3456;

help table tablea;

Now when you convert the column to char you get the format you want:

select cast(end_date as char(19)) from tablea;

    end_date
    29/10/2009 14:59:31
link|flag
Hi Carlos, Thanks for your response, I am particularly looking for this format to update in the table ( format 'dd/mm/y4bhh:mi:ss'). Please help me in updating the column in the above format. The datatype for end_date is column - type - format - maxlength END_date - TS - YYYY-MM-DDBHH:MI:SS - 19 Regards, TDHelp – TDHelp Oct 27 at 22:21
See my expanded explanation of output format. – Carlos A. Ibarra Oct 29 at 20:04

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.