Hi!
I have a date field and time field. Date field is in Julian format. I concatenate the two fields as follows:
TO_DATE(CAST(2415020 + CAST(TERM_DATE AS INT) AS CHAR(7)), 'J')|| ' ' || CAST( ((TERM_TIME /100) (FORMAT '99:99:99') (CHAR(11)) (TIME(0))
Now, when I tried to convert the concatenated value to TIMESTAMP format, I get the Invalid Timestamp error. The query is:
select CAST(TO_DATE(CAST(2415020 + CAST(COL_DATE AS INT) AS CHAR(7)), 'J')|| ' ' || CAST( ((COL_TIME /100) (FORMAT '99:99:99') (CHAR(11)) (TIME(0)) ) as varchar(20) ) AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS')from TableX
Can someone help me understand the issue here?
Hi,
I think your problem may be the mix of decimal places for seconds. You've got both TIME(0) and TIMESTAMP(6). In my experience these don't always 'play nice'.
I think you can remove the "(TIME(0))". At this point in the ocee you're concantenating values, so TD is really just using character data. Try something like:
select CAST(TO_DATE(CAST(2415020 + CAST(COL_DATE AS INT) AS CHAR(7)), 'J')|| ' ' || CAST( ((COL_TIME /100) (FORMAT '99:99:99') (CHAR(11))) as varchar(20) ) AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS')from TableX
Cheers,
Dave
What are the datatypes of TERM_DATE & TERM_TIME?
You can probably simplify your current calculation.
But I think your calculation should work as-is, are you sure there's no bad data in TERM_TIME?
Check it using
WHERE term_time / 1000000 NOT BETWEEN 0 AND 23 -- bad hours OR term_time / 10000 MOD 100 NOT BETWEEN 0 AND 59 -- bad minutes OR term_time / 100 MOD 100 NOT BETWEEN 0 AND 59 -- bad seconds