Won't load Dates correctly from DB2 Database...

[#1]

Hello,

So, I am loading data into QlikView from a DB2 database, and one field within the Database is a DATE field. However, when I load the data into QlikView, it does not recognise the date correctly.

Does anyone know how to fix this?

Thanks

[rajeshvaswani77]

Hi,

All dates would be text when it comes from the DB2 database, you would have to use makedate function and create a date.

tahnks,

Rajesh Vaswani

[Anonymous]

Beth,

See the function date#() - it tells QlikView script how to interpret the data as date.  For example:
1) if the format is YYYYMMDD, use date(date#(DateField, 'YYMMDD'))

2) if the format is MMDDYYYY, use date(date#(DateField, 'MMDDYY'))

etc.

I don't know the date format in DB2, hope the info above can help.

Regards,

Michael

[#4]

Okay so I have tried everything that you have suggested with no luck. I have even manipulated them to try and achieve my aim and yet QlikView still won't recognise my date field.

Any help?

[rajeshvaswani77]

Well, if the dates from DB2 are all strings then you need to treat them as string rather than date. Then you will end up stripping the date string to get the date month and year using the string functions like left, mid, right. Once you have done that you could use makedate where you will not pass the year month and date to get the date in QlikView. I am assuming that you have got all the data from DB2 in text extracts.

Hope this helps.

thanks,

Rajesh Vaswani

[jagan]

Hi,

Can you attach some sample dates, so that it would be helpful to give the solution.

Regards,

Jagan.

[#7]

Within my DB2 database I have a column called Process Date, where the Data Type is Date.

When I ask QlikView to load these, I write:

LOAD PROCESS_DATE as "PROCESS_DATE";

SQL SELECT PROCESS_DATE FROM TABLE;

Now, I have the following dates entered in my database:

02/06/2013

09/08/2013

18/08/2013

and before you say it's because it is not in the US form, the Database knows what is the day and what is the month. I have checked.

However, QlikView sees these days as the following:

06/00/2013

08/00/2013

08/00/2013

And it sees the dates like this or not at all when I use the previous methods mentioned.

[rajeshvaswani77]

Hi Could you please try to change the SQL statement that will bring the date to QlikView.

TO_DATE(PROCESS_DATE,'DD/MM/YYYY')

thanks,

Rajesh Vaswani

[jagan]

Hi,

Try this script

LOAD
Date(DAte#(PROCESS_DATE, 'DD/MM/YYYY'), 'MM/DD/YYYY') as "PROCESS_DATE";

SQL SELECT PROCESS_DATE FROM TABLE;

Regards,

Jagan.

[#10]

Hi have tried that, but when I select the drop down (it is in a multi-box object) nothing happens, no data is shown?