topic Re: Date format in QlikView

Date format

rdsuperlike — Sat, 21 Mar 2015 04:43:55 GMT

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;

Re: Date format

fashid — Sat, 21 Mar 2015 06:09:32 GMT

Hi,

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

Regards,

Nadeem

Re: Date format

robert_mika — Sat, 21 Mar 2015 06:13:25 GMT

The debugger should point you where the error occurs.

Could you post the error message?

Re: Date format

maxgro — Sat, 21 Mar 2015 07:08:49 GMT

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)

Re: Date format

rdsuperlike — Mon, 23 Mar 2015 17:20:01 GMT

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:

Re: Date format

maxgro — Mon, 23 Mar 2015 17:42:06 GMT

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;

Re: Date format

rdsuperlike — Mon, 23 Mar 2015 18:10:19 GMT

still gives same error