Invalid Timestamp Error

[NRK]

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?

[DaveWellman]

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

[dnoeth]

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