Date variable not working in SQl query calling through Qlik

[smandar91]

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:

[stevedark]

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

[smandar91]

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

1. 2025-05-08
2. 2025-05-08 00:00:00.000000
3. 45785

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

Any idea what am i missing here?

[stevedark]

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

[smandar91]

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. 

[smandar91 · Accepted solution]

thanks to @stevedark  and Chatgpt , Follwing works :

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

[stevedark]

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.