Date format

[rdsuperlike]

I am getting error in below code.

I am suspecting the error is related to date format.

Can someone help me here?

Any input would be appreciated

SET QDateFormat   = 'YYYY/MM/DD hh:mm:ss'  ;

NoConcatenate

DATE_C:

Load

DATE_CH ;

SQL

SELECT TRUNC(SYSDATE) as DATE_CH from dual; //(SELECT to_date('03/07/2015','mm/dd/yyyy')  from dual)

Let vDate =( peek('DATE_CH',0,DATE_C));

DROP Table DATE_C;

NoConcatenate

VERSIONDATE:

Load

VERSION_DATE;

SQL

select distinct BUILD_DT as VERSION_DATE from TIME_V where DAY_DATE = to_date('$(vDate)','$(QDateFormat)');

//(SELECT to_date('03/07/2015','mm/dd/yyyy')  from dual);

Let vVersionDate = num(peek('VERSION_DATE',0,VERSIONDATE));

DROP Table VERSIONDATE;

[fashid]

Hi,

You can handle the date formats in the qlikview load script rather than the Sql select statement.

Regards,

Nadeem

[robert_mika]

The debugger should point you where the error occurs.

Could you post the error message?

[maxgro]

is Oracle your db?

you have to match your vDate format with QDateFormat format

I think (guess) your qDateFormat should be (db format)

'DD/MM/YYYY HH:MI:SS'

but if you post the script (including set ...), the error and which db it should be simpler to answer; also add some trace for vDate (or debug as Mika suggested)

[rdsuperlike]

Oracle provider for OLE DB

SET QDateFormat   = 'YYYY/MM/DD hh:mm:ss'  ;

NoConcatenate

DATE_C:

Load

DATE_CH ;

SQL

SELECT TRUNC(SYSDATE) as DATE_CH from dual; //(SELECT to_date('03/07/2015','mm/dd/yyyy')  from dual)

Let vDate =( peek('DATE_CH',0,DATE_C));

DROP Table DATE_C;

NoConcatenate

VERSIONDATE:

Load

VERSION_DATE;

SQL

select distinct BUILD_DT as VERSION_DATE from TIME_V where DAY_DATE = to_date('$(vDate)','$(QDateFormat)');

//(SELECT to_date('03/07/2015','mm/dd/yyyy')  from dual);

Let vVersionDate = num(peek('VERSION_DATE',0,VERSIONDATE));

DROP Table VERSIONDATE;

The debbuger points at bold statement

and error is as below:

[maxgro]

this is a working example, table name and column name are different, DATE_KEY is an Oracle  date

SET DateFormat='DD/MM/YYYY';

SET QDateFormat  = 'DD/MM/YYYY'  ;

DATE_C:

Load DATE_CH;

SQL

SELECT TRUNC(SYSDATE)-1000 as DATE_CH from dual; //(SELECT to_date('03/07/2015','mm/dd/yyyy')  from dual)

Let vDate = date(floor(peek('DATE_CH',0,DATE_C)));

DROP Table DATE_C;

trace $(vDate);

VERSIONDATE:

NoConcatenate Load VERSION_DATE;

SQL

select distinct DATE_KEY as VERSION_DATE from D_DATE_NOW where DATE_KEY <= to_date('$(vDate)','$(QDateFormat)');

Let vVersionDate = num(peek('VERSION_DATE',0,VERSIONDATE));

DROP Table VERSIONDATE;

[rdsuperlike]

still gives same error