By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

Need help with some weird date manipulations

P: 2
Hi everyone. I'm new here, and I think I've got a pretty unique problem (haven't found any solution to this anywhere else), but I'm hoping that someone here can help me.

To be honest, I'm not a DB2 guy. I'm a contractor working for a client who uses a very old AS400 to do their payroll. They're running V5R3. I'm not sure if that refers to the version of DB2 or the version of the iSeries OS that we're running. Maybe they're the same thing. As you can tell I'm not very versed with the ins and outs of an AS400 or DB2 for that matter.

Anyway, I need to join two tables together on a date field. Sounds simple until you realize that neither of the dates are stored with the date datatype. They're all stored in numeric fields. One of the dates is a JDE quasi-julian date (Then the pattern is CYYDDD where C is the century -- 0=19, 1=20, 2=21 -- and DDD is a number between 1 and 365. So 107248 would be the 248th day of 2007). The other date is really held in two separate numeric fields. One holds the year (only the last two digits), and the other contains the month/day as a four digit number. So 531 (really 0531) is May 31st.

I figure my best bet is to turn the month/day field from table 1 into a date (year doesn't really matter so it can be this year, last year, whatever), and then use the DayOfYear function and turn that date into a number between 1 and 365. Then I can join the tables on that and a mod of that other quasi-julian date since the last three digits are always going to be the day of the year.

Confusing?

Well, the good news is that I sorta have something working, but herein lies my problem. My query works when I run it from within the iSeries navigator, but does not work when I try to run the query using the in-built JDBC driver.

Here's what I've come up with so far:

dayofyear(date('1899-' || char(digits(decimal(int(a.gldt/100),2,0))) || '-' || char(digits(decimal(mod(a.gldt,100),2,0)))))

This also works in iSeries:

dayofyear('1899-' || char(digits(decimal(int(a.gldt/100),2,0))) || '-' || char(digits(decimal(mod(a.gldt,100),2,0))))

notice the lack of the date() function. Anyway both of those run fine from the iSeries navigator.

When I run this from my web server (ColdFusion 6.1) using the IBM supplied JDBC driver (jt400.jar), I get the following error:

[SQL0181] Value in date, time, or timestamp string not valid.

Can anyone tell me what gives? I'm at a loss.

If it will help to see my entire query, here it is:

select distinct c.ytprtr as TransactionNumber, decimal((c.ytphrw/100),9,2) as HoursWorked, c.ytdwk as DateWorked, a.MedNet as AmountBilled, decimal((c.ytgpa/100),9,2) as AmountPaid, a.pnam as EmployeeName, a.cust# as ClientNumber, a.inv# as InvoiceNumber, char(digits(decimal(c.ytfy,2,0))) || char(digits(decimal(c.ytpn,2,0))) as dateFromPayroll, char(digits(decimal(a.pyear,2,0))) || char(digits(decimal(int(a.pdate/100),2,0))) as dateFromBilling, c.ytan8 as AccountNumber, a.pss# as SSN, a.orgoff as Office

from qs36f.nf157pf as a inner join
nfpayr73.f060116 as b on a.pss# = int(b.yassn) inner join
nfpayr73.f0618 as c on b.yaan8 = c.ytan8 and dayofyear('1899-' || char(digits(decimal(int(a.gldt/100),2,0))) || '-' || char(digits(decimal(mod(a.gldt,100),2,0)))) = mod(c.ytdwk,1000)

where 1=1
and int(a.pdate/100) = 7
and a.pyear = 7
and c.ytan8 = 513389

order by transactionnumber

I sure someone can help me out on this. :o(

Thanks heaps,
Chris
Sep 10 '07 #1
Share this Question
Share on Google+
1 Reply


P: 2
Okay, if anybody cares here's the solution to this problem:

I upgraded my jt400.jar from JTOpen 4.4 to JTOpen 6.0, but that didn't help. Then it occurred to me that it may be having trouble with a date so far in the past. I changed '1899-' to '2007-' in my complicated date manipulations and the query worked just fine.

Grr... I still don't quite understand why the query worked in iSeries, but not from my ColdFusion application using both an outdated *and* current version of the jt400.jar driver.

Oh well. That's how I fixed it anyway. :o)

Chris

Hi everyone. I'm new here, and I think I've got a pretty unique problem (haven't found any solution to this anywhere else), but I'm hoping that someone here can help me.

To be honest, I'm not a DB2 guy. I'm a contractor working for a client who uses a very old AS400 to do their payroll. They're running V5R3. I'm not sure if that refers to the version of DB2 or the version of the iSeries OS that we're running. Maybe they're the same thing. As you can tell I'm not very versed with the ins and outs of an AS400 or DB2 for that matter.

Anyway, I need to join two tables together on a date field. Sounds simple until you realize that neither of the dates are stored with the date datatype. They're all stored in numeric fields. One of the dates is a JDE quasi-julian date (Then the pattern is CYYDDD where C is the century -- 0=19, 1=20, 2=21 -- and DDD is a number between 1 and 365. So 107248 would be the 248th day of 2007). The other date is really held in two separate numeric fields. One holds the year (only the last two digits), and the other contains the month/day as a four digit number. So 531 (really 0531) is May 31st.

I figure my best bet is to turn the month/day field from table 1 into a date (year doesn't really matter so it can be this year, last year, whatever), and then use the DayOfYear function and turn that date into a number between 1 and 365. Then I can join the tables on that and a mod of that other quasi-julian date since the last three digits are always going to be the day of the year.

Confusing?

Well, the good news is that I sorta have something working, but herein lies my problem. My query works when I run it from within the iSeries navigator, but does not work when I try to run the query using the in-built JDBC driver.

Here's what I've come up with so far:

dayofyear(date('1899-' || char(digits(decimal(int(a.gldt/100),2,0))) || '-' || char(digits(decimal(mod(a.gldt,100),2,0)))))

This also works in iSeries:

dayofyear('1899-' || char(digits(decimal(int(a.gldt/100),2,0))) || '-' || char(digits(decimal(mod(a.gldt,100),2,0))))

notice the lack of the date() function. Anyway both of those run fine from the iSeries navigator.

When I run this from my web server (ColdFusion 6.1) using the IBM supplied JDBC driver (jt400.jar), I get the following error:

[SQL0181] Value in date, time, or timestamp string not valid.

Can anyone tell me what gives? I'm at a loss.

If it will help to see my entire query, here it is:

select distinct c.ytprtr as TransactionNumber, decimal((c.ytphrw/100),9,2) as HoursWorked, c.ytdwk as DateWorked, a.MedNet as AmountBilled, decimal((c.ytgpa/100),9,2) as AmountPaid, a.pnam as EmployeeName, a.cust# as ClientNumber, a.inv# as InvoiceNumber, char(digits(decimal(c.ytfy,2,0))) || char(digits(decimal(c.ytpn,2,0))) as dateFromPayroll, char(digits(decimal(a.pyear,2,0))) || char(digits(decimal(int(a.pdate/100),2,0))) as dateFromBilling, c.ytan8 as AccountNumber, a.pss# as SSN, a.orgoff as Office

from qs36f.nf157pf as a inner join
nfpayr73.f060116 as b on a.pss# = int(b.yassn) inner join
nfpayr73.f0618 as c on b.yaan8 = c.ytan8 and dayofyear('1899-' || char(digits(decimal(int(a.gldt/100),2,0))) || '-' || char(digits(decimal(mod(a.gldt,100),2,0)))) = mod(c.ytdwk,1000)

where 1=1
and int(a.pdate/100) = 7
and a.pyear = 7
and c.ytan8 = 513389

order by transactionnumber

I sure someone can help me out on this. :o(

Thanks heaps,
Chris
Sep 10 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.