Invalid Timestamp Error

Database
NRK
Visitor

Invalid Timestamp Error

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?

2 REPLIES
Senior Apprentice

Re: Invalid Timestamp Error

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

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Invalid Timestamp Error

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