topic Re: Date variable not working in SQl query calling through Qlik in Connectivity & Data Prep

Date variable not working in SQl query calling through Qlik

smandar91 — Thu, 08 May 2025 10:46:06 GMT

I'm trying one direct query in Qlik as i need real time data. Everything seems fine only variable is not working in Sql query.

This is expeted date format: SET DateFormat='M/D/YYYY';

Sql query where its working -->where TRANSACTION_DATE=TRUNC(SYSDATE)

Sql query where its failing -->where TRANSACTION_DATE= TRUNC('$(vLastWorkingDate)')

Can you elaborate same?

Last working date:

Re: Date variable not working in SQl query calling through Qlik

stevedark — Thu, 08 May 2025 12:45:59 GMT

Hi @smandar91

The TRUNC statement in that case is removing the time from the date, so is not required as last working date does not have a time.

I would try getting a query to work without the variable first, hardcoding the date.

The exact syntax will depend on the flavour of SQL. I would try having the date as 2025-05-08, for instance, or removing the quotation marks and putting the date in as a number, today is 45785.

Once you have got this working with a hardcoded value you can then get the variable in the correct format, either using the date function or multiplying the value by 1.

Hope that helps point you in the right direction.

Steve

Re: Date variable not working in SQl query calling through Qlik

smandar91 — Mon, 12 May 2025 07:01:29 GMT

Hi Thanks for revert , This is how SQL date is coming in Qlik if i don't do any transformation Transaction Date = 2025-05-08 00:00:00.000000

Tried with following hardcoded values

2025-05-08
2025-05-08 00:00:00.000000
45785

With quotes without quotes. still no success. There is data for following date in table.

Any idea what am i missing here?

Re: Date variable not working in SQl query calling through Qlik

stevedark — Mon, 12 May 2025 07:07:25 GMT

Hi @smandar91

Which flavour of SQL is it that you are using? This will inform the syntax that you need to use.

Trying the query in the query analyser that comes with the database will be a good start, to check that you have the syntax and date format correct.

Steve

Re: Date variable not working in SQl query calling through Qlik

smandar91 — Mon, 12 May 2025 07:32:15 GMT

Unfortunately, i don't access to Db. not sure if this helps

Also tried this part

Let vLastWorkingDate = timestamp(date(Peek('NewPBDate', 0, 'Final_Cal')),'YYYY-MM-DD hh:mm:ss.ffffff'); Still the same.

Re: Date variable not working in SQl query calling through Qlik

smandar91 — Mon, 12 May 2025 08:01:45 GMT

thanks to @stevedark and Chatgpt , Follwing works :

TRANSACTION_DATE = To_TIMESTAMP('$(vLastWorkingDate)', 'YYYY-MM-DD HH24:MI:SS.FF6')

Re: Date variable not working in SQl query calling through Qlik

stevedark — Mon, 12 May 2025 08:05:54 GMT

Hi @smandar91

Thanks for letting me know it's now working. Pleased to be mentioned in such esteemed company as ChatGPT!

You appear to be using Oracle, which does have it's own peculiarities. I should have guessed at this from the upper case field name in your query.

Glad it's working now.