How to keep date format when creating Index on a Table

20 pts.
Tags:
SQL Database Indexing
sql indexes
SQL statements
SQL tables
I have used CREATE TABLE to create a PF which contain a DATE field. And then use CREATE INDEX to create a LF with data selections. However, when I check the DATE field, I find that, in the PF, its Date Format is *ISO; but in the LF, its Date Format has been changed to *DMY. How could I keep the Date Format when I creating the LF using CREATE INDEX then? The SQL statements are as below: CREATE TABLE APMTEST/APTMOD ( ProductCode for column APOPRDCDE CHAR(4) DEFAULT ' ' NOT NULL, ProcessSubType for column APOSUBTYP CHAR(16) DEFAULT ' ' NOT NULL, ContactType for column APOCNTTYP CHAR(16) DEFAULT ' ' NOT NULL, EffectiveDate for column APOEFFDAT DATE DEFAULT CURRENT_DATE NOT NULL, ExpiryDate for column APOEXPDAT DATE DEFAULT CURRENT_DATE NOT NULL, ModelNumber for column APOMODEL CHAR(16) DEFAULT ' ' NOT NULL, Status for column APOSTS CHAR(1) DEFAULT ' ' NOT NULL, PRIMARY KEY (ProductCode, ProcessSubType, ContactType, EffectiveDate) ) RCDFMT APTMODR ; label on column APMTEST/APTMOD ( ProductCode text is 'Product Code', ProcessSubType text is 'Process SubType', ContactType text is 'Contact Type', EffectiveDate text is 'Effective Date', ExpiryDate text is 'Expiry Date', ModelNumber text is 'Model Number', Status text is 'Status' ); CREATE INDEX APMTEST/APTMOD1 ON APTMOD ( APOPRDCDE, APOSUBTYP, APOCNTTYP, APOEFFDAT) WHERE APOSTS = ' ' RCDFMT APTMODR ;

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’ve found out the reason why the Date Format has changed:

That’s because there is a option in RUNSQLSTM command to specify Date Format which is default to *JOB.

What is needed to do is change that option to *ISO, then problem is solved!

Discuss This Question: 2  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
  • Meandyou
    Glad you found the answer. Funny, isn't it, that when we explain the problem we often see the solution. Happens to me all the time.
    5,220 pointsBadges:
    report
  • Chippy088
    I always have maintained that a problem well stated is a problem half solved.
    4,625 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