date format error

[rdsuperlike]

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]

Hi

Use to_char instead of to_date

Hope that works.

Regards

Av7eN

[petter]

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]

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')

;