How To Do A Date Range on a PACK field type

90 pts.
Tags:
pack
I am learning how to use the Data Transfer From iSeries box. I have a procedure date field that is stored as a PACK data type. How do I do a date range in my Where clause on it? Cyndi

Software/Hardware used:
AS400 iSeries 5.5
ASKED: June 21, 2013  7:59 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    We don't know what the data in your date (packed) field looks like but if it's 8 wide with 0 decimals and is in CCYYMMDD format, then your where clause might look like this. Where yourdate between 20130101 and 20130531
    48,595 pointsBadges:
    report
  • TomLiotta
    We need to know the format of any "date" value that would be stored in a packed field. Phil's suggestion should work if the "date" is formatted usefully. . If it was stored in an actual DATE column, it would be more efficient (faster, take less space, etc.) and be much more certain. . Tom
    125,585 pointsBadges:
    report
  • cpemtp
    My apologies... I didn't realize there were multiple ways to program a pack field. My field is set as MDDYY. This causes a problem because I can have 1/1/12 as 10112 but then 12/1/12 is in there as 120112. I'm guessing I'll have to do some kind of conversion first before using the "between this date and that date" code.
    90 pointsBadges:
    report
  • philpl1jb
    where substring(myField,5,2) || substring(myField,1,2) || substring(myField,3,2) between '130101' and '130531' Notes: using the substring will convert the fields to character which must be compared with character or where CAST(substring(myField,5,2) || substring(myField,1,2) || substring(myField,3,2) as Decimal) between 130101 and 130531 Note: this cases the result as a numeric which can be compared with numeric range. Either should work.
    48,595 pointsBadges:
    report
  • cpemtp
    geez...that is alot to go through to just get a date range...but I guess I'll have to get use to it...thanks for your help.
    90 pointsBadges:
    report
  • philpl1jb
    Sorry.
    48,595 pointsBadges:
    report
  • TomLiotta
    Skip this initial soapbox rambling if you wish...
    This highlights a major difference between storing 'date values' in actual DATE columns vs. numeric or character columns. The physical storage of a DATE data type is a 4-byte integer, as a "Scaliger" number. Physical storage of an 6-digit packed value is also a 4-byte area.
    .
    It not only takes as much physical space to store as packed, but DB2 can't provide good protection against invalid 'date values' from being stored. Nor can any reliable date operations be done on the value without first doing a conversion to some useful DATE format.
    .
    DATE values are automatically converted to external (human readable) formats by internal DB2 code. That code is highly optimized and extremely widely tested. It runs "closer to the metal" than any code that can be written into a compiled RPG, COBOL or C program.
    .
    Maybe the worst part is the ugly code needed to do 'date' comparisons such as those needed for date ranges. It's somewhat okay when the packed value is stored in 8-digits as YYYYMMDD. That provides a useful index for searching.
    .
    It brings different problems when date durations are needed. Finding dates like 30-, 60- or 120-days in the future or past takes ugly programming. And again the programming is farther from the internal database code, is less optimized and is less tested (by other users around the world).
    Now the specifics...
    After those things, there are the real-life cases we all have to handle. You have a file that has date values stored as MMDDYY and you need to work with what you have.
    .
    To make valid comparisons, you need 'YY' to be first. And if there is any possibility of dates crossing from before the year 2000 to after 1999, it has to be converted to 'YYYY'. If the only YY values are from 00 through 13, it won't really if two or four digits are used.
    .
    But because you have MM in your left-most positions, you have to handle the difference that happens for months 1 through 9 and months 10 through 12. When SUBSTR() is used, it should be done over DIGITS() rather than over the plain numeric value: The DIGITS() function will handle the leading zeros that can disappear when the numeric values are converted to characters.

    where substring(digits(myField),5,2)
        concat substring(digits(myField),1,2)
        concat substring(digits(myField),3,2)
     between ’130101′ and ’130531′
    .
    An alternative could be to create a VIEW that does conversion for you on the server. The VIEW can be kept for later use at any time by anyone who needs it. It would convert the packed value into a DATE so that real dates are what you actually see. The new value could be used in place of the old one, or it could be an additional available column.
    .
    Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    BTW, the positions for the SUBSTR() function depend on how many digits are defined for the packed field. They should be correct if the field is defined for six digits. . Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    As usual Tom is correct ..the digits function is required where substring(digits(myField),5,2) || substring(digits(myField),1,2) || substring(digits(myField),3,2) between ’130101′ and ’130531′ and this could be shortened because MMDD are in the correct order to: where substring(digits(myField),5,2) || substring(digits(myField),1,4) between ’130101′ and ’130531′ Phil
    48,595 pointsBadges:
    report
  • cpemtp
    thanks for all your help...series has been in place here for about 25 years so unfortunately I don't know why they created the admission date field as a pack field instead of a true date field. I'll just have to work with what I have and use the string you have given me to extract date ranges.
    90 pointsBadges:
    report
  • TomLiotta
    I figured this was a 'legacy' kind of problem. That's why I went into the "real-life cases" part. Many of us run into these things that are left over from before "DATE" data types existed. If we don't get project approvals to replace old stuff with new stuff, we spend our time doing what you ask about. . Back when your files were created, true "DATE" data types either didn't exist or were so new that the developers hadn't learned about them. It's just one way we're stuck wasting time nowadays. . Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following