SQL Load Script in Qlik Sense

[crichter14]

Question specifics:  Answers must be for Qlik Sense not Qlik View, please.  This is in the load script, not in an object.  I have a variable var_CurrentYearStart compared to a field AUDTIME.  It's not going to work, because the AUDTIME field is a timestamp and the variable is a number (#####).

In the SQL load script, how can I change the AUDTIME timestamp to a numeric value that I can use.  The normal Qlik Syntax won't work because DATE and NUM are not recognized by the SQL.  Ideas?

Code below:

SQL SELECT

ROWNUM AS AUDIT_ID,

C.COL AS AUDIT_FIELDNAME,

T.AUDTIME AS AUDIT_TIME

T.TBL AS AUDIT_TABLENAME,

FROM AUD_COL C
LEFT JOIN AUD_TBL T ON C.AUD_TBLIDX=T.AUD_TBLIDX
WHERE T.AUDTIME >=$(var_CurrentYearStart) AND T.TBL IN ('SORD','SORDD','SOZD') AND C.COL IN ('ALLOQ','PACKQ');

Thanks guys, I'm struggling with this one.

Cheryl

[Carlos_Reyes]

I guess this is a SQL question and not a Qlik one, isn't it?

https://stackoverflow.com/questions/23294562/convert-timestamp-to-number-format

[treysmithdev]

This depends on the database you are querying, since different vendors have different functions to convert datatypes. 

Can you convert the variable to a timestamp? It would be faster converting the one variable to a timestamp rather than the database convert every value in the field during its comparison. You can do the same through SQL by wrapping $(var_CurrentYearStart) with the appropriate SQL function.

[crichter14]

Close Carlos, but I need it to be a number like the format of a NUM(DATE(field)) in Qlik.  This number comes back like 20190723 but I need it to be 43669.   Thank you for responding.

[crichter14]

Hi Trey,

I've tried variations, and code that should work isn't.  I think it's because its an SQL where clause rather than Qlik so it's not working.  These are my fails:

TO_DATE($(var_CurrentYearStart))

TO_TIMESTAMP($(var_CurrentYearStart))

CAST($(var_CurrentYearStart) AS DATETIME)

In SQL, I should be able to use 

Convert(int, cast(ONDATE AS datetime))

on the timestamp field, but it didn't work.  I'm assuming because Qlik doesn't like the INT or the DATETIME.

I appreciate any help, I've had a hard time.  We're using an ODBC connection if that helps.

[treysmithdev]

Let's convert your variable to the timestamp string first:

Let var_CurrentYearStart = Floor(YearStart(Today()));
Trace ------------------------------- Year Start Num: $(var_CurrentYearStart);

Let var_CurrentYearStart_TS = Text(Timestamp($(var_CurrentYearStart),'YYYY-MM-DD hh:mm:ss'));
Trace ------------------------------- Year Start TimeStamp: $(var_CurrentYearStart_TS);

Then in your SQL query you can cast a CHAR to a TIMESTAMP:

TO_TIMESTAMP('$(var_CurrentYearStart_TS)', 'HH24:MI:SS')

[treysmithdev · Accepted solution]

Oops, should've been: 

TO_TIMESTAMP('$(var_CurrentYearStart_TS)', 'YYYY-MM-DD HH24:MI:SS')

[crichter14]

That worked thank you Trey!  I did have to add it to the WHERE clause too, but thank you so much!