Date format issue

[#1]

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

[erichshiino]

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

[#3]

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