topic Re: Cannot get Qlik Sense to convert SQL Timestamp in App Development

Cannot get Qlik Sense to convert SQL Timestamp

nigelapthome — Mon, 21 Aug 2023 19:53:36 GMT

Hi Folks

I have a direct connection to a SQL database for a basic select statement:

SQL SELECT

Id as [Alert ID],

TimeRaised AS [Alert Time Raised]

From xyz

The timestamp returned is like this (Year-Month-Day Time):

2023-08-18 18:00:08.907000

I need the format to be 2023-Aug-18 18:00 PM

I set the Time Format like this

SET TimestampFormat='YYYY-MM-DD hh:mm:ss tt';

But Qlik Sense is not recognizing the incoming timestamp and does not convert it like I need.

Be grateful for any advice.

Thanks

Nigel

Re: Cannot get Qlik Sense to convert SQL Timestamp

Zapparoli — Mon, 21 Aug 2023 19:58:05 GMT

Hi @nigelapthome

Have you tried using the Timestamp Function ?

Since you already changed the format on the Set variable, simply using Timestamp(TimeRaised) AS [Alert Time Raised]) should work.

Let me know if it helps.

-Zapparoli

Re: Cannot get Qlik Sense to convert SQL Timestamp

BrunPierre — Mon, 21 Aug 2023 20:51:57 GMT

Hi, perform the conversion in a preceding load, above the SELECT statement like this.

LOAD Id as [Alert ID],
Timestamp(Timestamp#(TimeRaised ,'YYYY-MM-DD hh:mm:ss.fffffff'),'YYYY-MMM-DD hh:mm:ss TT') as [Alert Time Raised];

SQL SELECT
Id,
TimeRaised
From xyz;

Re: Cannot get Qlik Sense to convert SQL Timestamp

nigelapthome — Mon, 21 Aug 2023 21:16:19 GMT

No luck Matheus

It is not seeing the SQL date as a date format.

I also tried changing the format in SQL :

Convert(varchar,TimeRaised,20) AS [Alert Time Raised]

Running it in SQL Server Management gives this format:

2023-08-20 00:28:15 (YYYY-MM-DD HH:MM:SS)

but again QlikSense does not see to accept this.

Re: Cannot get Qlik Sense to convert SQL Timestamp

marcus_sommer — Tue, 22 Aug 2023 07:18:42 GMT

Each inbuilt conversion will only work if the applied format-pattern fits to 100% to the data. To get this data you need to load them as they are into Qlik (and only these data - if you had loaded any values into this field within a previous load - it could have an impact) and then using a table-box to look at them and/or to export it to an editor like Notepad++. Also possible is to loop within the script though each single char to track their value.

Looking at the values within the data-base may trick you because they mustn't show the stored values else any conversion/formatting. Further the used driver could apply any change to the data.

Beside of this you may also consider a bit tedious approach by using string-functions like keepchar(), trim(), subfield(), ... to cut the date- and time-parts and converting/formatting those results.