Date format for DB2 database

[ipauldur]

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

[#2]

Hi,

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

Thanks & Best Regards,

Kuldeep Tak

[#3]

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

[johnw]

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?

[#5]

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

[johnw]

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

[#7]

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

[johnw]

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.

[#9]

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

[#10]

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