topic date format error in QlikView

date format error

rdsuperlike — Tue, 24 Mar 2015 17:54:46 GMT

Hi,

I am trying to pass variable vDate in my sql in script. But somehow $(vDate)-5 doesnt seem to work.Can someone help me here?

select distinct abc from (

select distinct

xyz, abc, ert,zqw,wer,qwt

from t1, t2

where name = 'PLN'

and trunc(day_date) >=trunc(to_date('$(vDate)-5','$(QDateFormat)'))// --'08-Mar-2015'//--trunc(sysdate-5)

and trunc(day_date) <= trunc(to_date('$(vDate)','$(QDateFormat)'))// --'13-Mar-2015'//--trunc(sysdate)

group by name, xyz

);

aveeeeeee7en — Tue, 24 Mar 2015 18:01:30 GMT

Use to_char instead of to_date

Hope that works.

Regards

Av7eN

petter — Tue, 24 Mar 2015 18:41:03 GMT

I think that the following part of your SQL creates a problem:

trunc(to_date('$(vDate)-5','$(QDateFormat)'))

I am pretty sure the it will be like this after QlikView has done it's $-sign exansion:

trunc(to_date('08-Mar-2015-5',.......

And your SQL database (Oracle I presume...) will be left to try to interpret what 08-Mar-2015-5 means.... It will not

subtract 5 days from 8th of March which you intend...

I think this may be the syntax you have to use:

trunc( to_date( '$(vDate)' , '$(QDateFormat)' ) - interval '5' days )

Hope this helps...

maxgro — Tue, 24 Mar 2015 19:07:53 GMT

let vDateMinus5 = date(today()-5, 'YYYYMMDD');

TRACE $(vDateMinus5);

SQL SELECT

DAY_DATE,

to_char(DAY_DATE, 'YYYYMM') as DAY_DATE_CHAR

FROM TEST.TEST

where trunc(DAY_DATE) <= to_date('$(vDateMinus5)', 'YYYYMMDD')

;