topic Date format issue in QlikView

Date format issue

Thu, 16 Jun 2011 06:19:51 GMT

I use following query to extract and format the date from Oracle. When I run this in Qlikview 10 dev. on 2 following desktops I get different results as follows.

1. Desktop returns for eg DATE_FYYYY as 0030,0031,0032 Other Qlikview 10 desktops returns 2009,2010,2010 i.e the YYYY is giving me the problem.

When the following query is executed in oracle client from both the desktops, it gives me appropriate Year 2009,2010,2011.

Driver used is 1st desktop is OLEDB provider for Oracle , in other desktop it is Microsoft

OLEDB provider for Oracle. I also tried replacing YYYY with RRRR.

Not sure how to correct this. Let me know if anybody from you could provide some insight into this issue.

DATEMASTER:

LOAD

EXTRACTIONDATE,

DATE_MM,

DATE_CYYYY,

DATE_CYYYYMM,

DATE_MON,

DATE_FYYYY,

DATE_FQ,

DATE_FYYYYQ,

DATE_FYYYYMM;

SELECT

dt ExtractionDate,

to_char(dt,'MM') DATE_MM,

to_char(dt,'YYYY') DATE_CYYYY,

to_char(dt,'YYYY-MM') DATE_CYYYYMM,

to_char(dt,'Mon') DATE_Mon,

to_char(add_months(dt,-3),'YYYY') DATE_FYYYY,

'Q'||to_char(add_months(dt,-3),'Q') DATE_FQ,

to_char(add_months(dt,-3),'YYYY')||'-Q'||to_char(add_months(dt,-3),'Q') DATE_FYYYYQ,

to_char(add_months(dt,-3),'YYYY-MM') DATE_FYYYYMM

from (SELECT ADD_MONTHS('30-APR-2009',LEVEL-1) dt FROM DUAL CONNECT BY LEVEL <= 26);

Date format issue

erichshiino — Fri, 17 Jun 2011 02:11:09 GMT

You could transform the dates on QlikView instead of the database

You can use it for all your fields, I will change just the one with problems now:

DATEMASTER:

LOAD

EXTRACTIONDATE,

DATE_MM,

DATE_CYYYY,

DATE_CYYYYMM,

DATE_MON,

date ( addmonths( ExtractionDate,-3), 'YYYY') AS DATE_FYYYY, // It ignores the DATE_FYYYY from database

DATE_FQ,

DATE_FYYYYQ,

DATE_FYYYYMM;

SELECT

dt ExtractionDate,

to_char(dt,'MM') DATE_MM,

to_char(dt,'YYYY') DATE_CYYYY,

to_char(dt,'YYYY-MM') DATE_CYYYYMM,

to_char(dt,'Mon') DATE_Mon,

--to_char(add_months(dt,-3),'YYYY') DATE_FYYYY,

'Q'||to_char(add_months(dt,-3),'Q') DATE_FQ,

to_char(add_months(dt,-3),'YYYY')||'-Q'||to_char(add_months(dt,-3),'Q') DATE_FYYYYQ,

to_char(add_months(dt,-3),'YYYY-MM') DATE_FYYYYMM

from (SELECT ADD_MONTHS('30-APR-2009',LEVEL-1) dt FROM DUAL CONNECT BY LEVEL <= 26);

Hope this helps,

Erich

Date format issue

Fri, 17 Jun 2011 12:37:36 GMT

Thanks Erich for the work around, I can definitely use the qlikview dates but if this gets resolved I would be happier.