Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
QFanatic
Creator
Creator

Formatting of a TimeStamp Field in Qlik going weird

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...

 

 

 

Labels (1)
3 Replies
MayilVahanan

Hi @QFanatic 

Try like below

=Date(Date#(text(Sys_End_Tms), 'YYYY/MM/DD HH:mm:ss'),'YYYY/MM/DD')

Thanks & Regards,
Mayil Vahanan R
QFanatic
Creator
Creator
Author

Hello,

Thank you for your reply - unfortunately it doesn't work  - it returns nothing 😞

 

 

 

QFanatic
Creator
Creator
Author

I found this statement on Terdata help forum..

Sys_End_Tms - INTERVAL '1' SECOND AS SubtractOneSecond

 

I subtract one second..then Qlik is happy and returns 9999/12/31 23:59:59..

weird but true