Final question here.. Just alsow wanted to understand how the difference in timestamp can be converted to varchar(255)
sel(start_ts - end_ts) hour(4) to second(0) as diff from table
I wanted to insert into a table of a 'diff' column which has varchar field. Any lights on this?
Use CAST - standard SQL syntax for converting from one data type to another.
Ya I was able to do bove with tochar function. That's fine.
Got stuck with one more.
In the below query below are the datatypes,
dur - time(0)
start_ts - timestamp(6)
end_ts - timestamp(6)
Getting interval field overflow error when I execute select.
insert into table a(dur)sel CAST(SUBSTRING(('0000'||TRIM(CAST( (((start_ts - end_ts) HOUR(4) TO SECOND(0))) AS CHAR(11)))) FROM CHARS('0000'||TRIM(CAST( (((start_ts - end_ts) HOUR(4) TO SECOND(0))) AS CHAR(11))))-7) AS TIME(0)) AS dur from table b;
As Dave already said, the source columns are defined with a higher precision than SECOND(0). Thus you need to change the Substring accordingly to truncate the subseconds.
But this can be simplified to:
TIME '00:00:00' + (endtime - starttime HOUR(4) TO SECOND(6))