I am reading from a Teradata db.
I have two fields called Sys_Strt_Tms and Sys_End_Tms - both are defined on the db as Timestamp (6) fields.
In the Sql part of my script I am reading the fields like this:
TO_CHAR(Sys_Strt_Tms, 'YYYYMMDD HH24:MI:SS') as Sys_Strt_Tms - returns e.g. 20210412 23:25:28 - which is correct
TO_CHAR(Sys_End_Tms, 'YYYYMMDD HH24:MI:SS') as Sys_End_Tms - also usually populates with a value that is right...EXCEPT when the SQL value of that field is = 9999/12/31 23:59:59 (which is a default value if nothing is populated) . Then it returns, in Qlik 10000/01/01/ 00:00:00 (Directly from sql, its fine - 99991231 23:59:59. I am guessing its because of some overflow and that I'd need to use something like 'Floor'.
In my Qlik Script, I have tried to format those fields as timestamp (using Timestamp#(Sys_End_Tms ,'YYYYMMDD hh:mm:ss') and then it returns , for the Sys_Strt_Tms - 44298,976023...., and for the Sys_End_Tms it returns 2958466..
In conclusion, it looks like the Teradata Engine returns the field for Sys_Strt_Tms , but for Sys_End_Tms, after Qlik reads it, there is an error with the default value.
I am sure it has to do with the "overflow" on that field, for value 9999/12/31 23:59:59, but I don't know how to format it correctly.
(Afterthought - I need to subtract these 2 fields from each other at a later stage to get a time diff, so I assume they need to be timestamp fields to be able to achieve that?
Much appreciated.
Here is a summary of what happens:
Load
Timestamp#(Sys_Strt_Tms, 'YYYYMMDD hh:mm:ss') as Sys_Strt_Tms_1 , returns 44298,976023495
Timestamp#(Sys_End_Tms, 'YYYYMMDD hh:mm:ss') as Sys_End_Tms_1, returns 2958466
Sys_Strt_Tms, returns 2021/04/12 23:25:28
Sys_End_Tms; returns 10000/01/01 00:00:00 when the field = default value of 9999/12/31 etc
Select
TO_CHAR(EV.Sys_Strt_Tms, 'YYYYMMDD HH24:MI:SS') as Sys_Strt_Tms,
TO_CHAR(EV.Sys_End_Tms,'YYYYMMDD HH24:MI:SS') as Sys_End_Tms
from
Table...
