Cannot get Qlik Sense to convert SQL Timestamp

[nigelapthome]

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

[Zapparoli]

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

[BrunPierre]

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;

[nigelapthome]

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.

[marcus_sommer]

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.