topic Re: Question concerning Date and Long Timestamps in SQL Database in QlikView

Question concerning Date and Long Timestamps in SQL Database

qliksase — Mon, 04 Sep 2017 13:14:53 GMT

Hi dear Qlik Community,

before I start with my question, I wanted to say that the Qlik Community is maybe the best online help forum on the internet.

I need to load data from an SQL Server and put it into a QVD. I have serious problems with a Timestamp field.

These are the options concerning the field:1

This is how the data looks on the SQL Database:

This is how it looks in my QVD File:

Here is my Statement:

OEE_RES_STATE_SAMP:
LOAD text(RSS_TIMESTAMP_START) as NewDate;

SQL SELECT "RSS_TIMESTAMP_START"
FROM xxx.dbo."xxx_xxx_xxx_xxx";

All I want to do is to make a date out of this timestamp -> yyyy-mm-dd hh:mm:ss.fff

Would anybody please assist me ?

Thank you in advance

Re: Question concerning Date and Long Timestamps in SQL Database

sergio0592 — Mon, 04 Sep 2017 17:51:55 GMT

Hi, try with :

OEE_RES_STATE_SAMP:

LOAD timestamp#(RSS_TIMESTAMP_START,'YYYYMMDDhhmmss') as NewDate;

But i don't know what are your last three digits? Mili seconds?

Re: Question concerning Date and Long Timestamps in SQL Database

qliksase — Wed, 06 Sep 2017 07:46:00 GMT

Thank you for the fast response. The hint didn't work, I think the problem is the length of this numeric value in the database. But I found a different solution in the meantime:

LOAD date#(Left(((RSS_TIMESTAMP)/100000),8), 'YYYYMMDD') as %Date

By using this function I get a correct date.

Thank you for your help.

Re: Question concerning Date and Long Timestamps in SQL Database

Wed, 06 Sep 2017 07:59:23 GMT

"before I start with my question, I wanted to say that the Qlik Community is maybe the best online help forum on the internet."
this is so true

(just saying)

hope i can make my own document soon since i'm new here in qlikview