× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Buck, Charles, Rob and Gary.

I'm going to take all your answers into consideration and over the weekend try and resolve the issue with a totally sql solution.  Thanks for all the food for thought.

@Charles, we do have DBU and I tried DBUJRN.  Nice, I'll keep that in mind.  I never knew DBU did that.  Thanks for the tip.

Thanks everyone,

Rob

On 2/22/2019 3:21 PM, Buck Calabro wrote:
On 2/22/2019 2:46 PM, Robert Rogerson wrote:
Charles, then my understanding of something is incorrect (wouldn't be
the first time).

Here is a date field in one file in DSPFFD

              Data        Field  Buffer    Buffer        Field
   Field      Type       Length  Length  Position        Usage
   SBDATE     DATE           10      10         1        Both
     Field text  . . . . . . . . . . . . . . . :  Sales Date
     Date Format . . . . . . . . . . . . . . . :  *ISO
     Default value . . . . . . . . . . . . . . :
         '0001-01-01'
     Coded Character Set Identifier  . . . . . :     37

And if I look at the file in WRKMBRPDM

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7
2018-02-260030607911615300007341002632 0000004000034507122870001-01-01
2018-02-260040607911615300007341002632 0000005000034507122880001-01-01
2018-02-260100607911615300007341002632 0000003000034507122890001-01-01
2018-02-260110607911615300007341002632 0000002000034507122900001-01-01


If I do the same for the file in question...

           Data        Field  Buffer    Buffer        Field
Field      Type       Length  Length  Position        Usage
FRDAT      DATE           10      10        11        Both
  Field text  . . . . . . . . . . . . . . . :  From Date
  Date Format . . . . . . . . . . . . . . . :  *USA
  Coded Character Set Identifier  . . . . . :     37

And if I look at the file in WRKMBRPDM

*...+....1....+....2....+....3
á█|█ä█¤71011/01/201812/31/2022
á█|█ä█¤71411/01/201812/31/2022
á█|█ä█¤71511/01/201812/31/2022

Am I misunderstanding something?
We tend to think that DSPPFM is showing us the 'raw' contents of the
table, but that's not the case. DSPPFM is interpreting the date column
and formatting it nicely for us to understand. So a *YMD gets formatted
differently to a *ISO. In both cases, the underlying (raw) data is in a
form that's ugly for us humans.

With respect to the actual problem, I've learnt the hard way that if I
have an SQL table, I should only manipulate it with SQL and not with CL
commands. You might consider SET OPTION DATFMT, or the scalar function
VARCHAR_FORMAT()
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzscavarcharformat.htm


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.