topic Date format for DB2 database in QlikView

Date format for DB2 database

ipauldur — Sun, 02 Aug 2009 14:22:32 GMT

Hi,

Is there any specific date format required while extracting data from DB2 database?

Because, I have extracted data from the DB2 database.In that data, date values are showing in numbers.

If we are using, mid,left or right function then i am not able to get proper date format for all values.

Is there any specific date format for DB2 db.

Thanks in advance,

Regards,

Durai

Date format for DB2 database

Mon, 03 Aug 2009 10:47:19 GMT

Hi,

May be the date# function can help you while reading the data.

Thanks & Best Regards,

Kuldeep Tak

Date format for DB2 database

Thu, 10 Sep 2009 22:09:52 GMT

Dear Dura,

Did you get the final solution for your problem with date format from a DB2 Database?

I can't get it to work correctly (year/month/week/day) even with the functions date or date#.

Thanks in advance,

Leo

Date format for DB2 database

johnw — Fri, 11 Sep 2009 02:25:44 GMT

It's all a matter of what you're using for your format in DB2, and what format you want the final result to be in. If you're specifically using the DB2 DATE format, I believe that's YYYY-MM-DD. So if you wanted your final result to be in DD/MM/YY, you would use date(date#(MyDB2Date,'YYYY-MM-DD'),'DD/MM/YY'). We're instead storing dates as integers in YYYYMMDD format, and want to see them in QlikView in MM/DD/YY format, so we use date(date#(MyDB2Date,'YYYYMMDD'),'MM/DD/YY').

So...

What does your DB2 date look like?

What do you want your date to look like in QlikView?

Date format for DB2 database

Fri, 11 Sep 2009 12:59:13 GMT

He John,

Thanks for the quick respond. I'll have to expand my first mail with some other info.

We're using JDEdwards on a DB2 database and JDEdwards writes the date in an Julian format, "CYYDDD" , the century after 1900, the year in that century and the Julian date within that year. Examples: 105031 - January 31, 2005 ,107263 - September 20, 2007 and 98129 - May 9, 1998

An SQL-expression for displaying dates in "normal format" is e.g. date(1900+(JDEDATE/1000),1,1)+val(right(str(field1),3))-1

What should be the Qlickview-statement for showing Julian dates in an format like "DD-MM-YYYY"?

Thanks in advance

Leo

Date format for DB2 database

johnw — Fri, 11 Sep 2009 23:52:24 GMT

OK, I don't think QlikView has a date formatting string to read it in directly, so we'll just build it exactly like you showed, just translated for QlikView. Two extra functions required, the first is floor() because otherwise the division will keep the decimal portion, and we'll fail to make the date. The second is the date() function to tell QlikView that this is a date, and the final format you want the date to be displayed in. You could skip the date formatting if that is your default date format in the script, but I usually specify it explicitly.

date(makedate(1900 + floor(JDEDATE/1000),1,1)+num(right(text(JEDATE),3))-1,'DD-MM-YYYY')

Date format for DB2 database

Fri, 18 Sep 2009 14:18:42 GMT

He John,

Thanks for the help, but still it's not working.

When slicing up the formula it appears that the function text(JDEDATE) returns an scripterror bij Qlikview.

What am I doing wrong?

Strangly enough the function date wthout any parameters on the field e.g. date(JDEDATE) will return me on a Julian date of 109254 a result of "16-2-0300" while using the default settings for dates. I can't explain.

With kind regards.

Leo

Date format for DB2 database

johnw — Mon, 21 Sep 2009 23:53:49 GMT

I'm not sure why text(JDEDATE) would fail. Text(109254) works fine for me.

As for what QlikView is doing when you say date(109254), it should be treating 109254 as the number of days since December 30, 1899. Figuring about 365.2425 days per year, that gives us 299.127 years. My machine is returning February 14, 2199, which is in the neighborhood. I'm not sure why you would be getting February 16, 300. My machine puts that at -584340.

Date format for DB2 database

Sat, 26 Sep 2009 18:01:58 GMT

Hello Leo,

This julian date problem was solved by a collegue of mine:

date(makedate(1900+mid(JDEDATE,1,len(JDEDATE)-3))+mid(JDEDATE,len(JDEDATE)-2,3)-1) AS Yourdate

Kind regard,

Frank

Date format for DB2 database

Sun, 30 May 2010 05:06:18 GMT

Mean JDE Date taken as String for QV, am I right to say so?

Date format for DB2 database

Fri, 11 Jun 2010 20:41:35 GMT

Here is another solution in converting a db2 date as part of the data load.

SUBSTR(DIGITS(DATEFIELD), 5, 2)||'/' || SUBSTR(DIGITS(DATEFIELD), 7, 2)||'/'||SUBSTR(DIGITS(DATEFIELD), 1, 4) AS dateName

Re: Date format for DB2 database

priyav24 — Thu, 30 Jan 2014 14:04:40 GMT

Try this

=Date(AddYears(Date(Date#('19'&Right(DateField,6),'YYYYMMDD'),'DD/MM/YYYY')

,100*Left(DateField,1)),'DD/MM/YYYY')