Flip Dates

110 pts.
Tags:
AS/400
AS/400 date format
Hi, I have a file with numeric field which contain date values. The file is huge in size and some of the date values are in DDMMYYYY format and rest of them are in YYYYMMDD. The program writing the date values in DMY is fixed recently but I need to flip the previous records which is in DDMMYYYY format to YYYYMMDD.

Answer Wiki

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

Add your answer…

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

    1,  What system are you on?

    2.  What type of tool would you like to use to fix the data?

    3.  Be more specific about the type of the data, numeric fields can be stored in different field types.

    49,850 pointsBadges:
    report
  • NicyChn

    1.Working on AS400 system.

    2. tool not sure(thought i could use a program logic or query to fix this.)

    3.Numeric field is 8S 0

    Example

    File1

    Field-->   Field1   Field2              Field3    Field4

    Text--->    Main    Date                 Time         Program Name

    Records--> 1        07112013         12336      Prgm1\

     

     

     

    110 pointsBadges:
    report
  • NicyChn

    Example

    File1

    Field-->   Field1   Field2              Field3        Field4

    Text--->    Main    Date                 Time         Program Name

    Records--> 1        06112013         12336      Prgm1

                       1        20131107          3154       Prgm2

                       1        20120101          3251       Prgm3

                       1        15052012          5567       Prgm1

                       1         20131107         6871       Prgm1

     

    Here Field1, Field2 and Field4 are key of a logical file

    Program 1 has date in DDMMYYYY format prior to 07112013. These needs to be changed to YYYYMMDD format. File has 120+ million records in total

     

     

    110 pointsBadges:
    report
  • TomLiotta

    Every date in DDMMYYYY format will almost certainly have either (20) or (19) in the fifth and sixth digits. Other values are possible, but you probably know what your date range is. The same value will be invalid if the format is assumed to be YYYYMMDD because there can be no month with those digits.

    A single SQL UPDATE statement should easily do the conversion simply by testing those digits.

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb

    In Sql

    test this

    Select

      Mydate, Substring(Mydate, 5, 4) || substrin(Mydate,3.2) || substring(Mydate, 1,2)

      From Myfile

     Where substring(Mydate,5,2) in ('19','20')

    If/when you get it to do what you want then you will want something like this to update the data.

    Update myfile

      Mydate = Substring(Mydate, 5, 4) || substrin(Mydate,3.2) || substring(Mydate, 1,2)

     Where substring(Mydate,5,2) in ('19','20')

    49,850 pointsBadges:
    report
  • NicyChn

    Thanks Tom

    110 pointsBadges:
    report
  • NicyChn

    Hi Philip,

    This select  statement is working only with the condition when date is '11022013'

    and not selecting  (when zeros or  first position is blanks for Myfield) when the dates are '08112013' , the date is shown as '8112013' in the Mydate field in Myfile.

    Eg:

     8,012,000
    20,130,824
    20,130,801
    21,121,988
    20,130,824

    select statement is  returning only 21121988.


     

    110 pointsBadges:
    report
  • TomLiotta

    Because leading zeros are possible, the MYDATE column must be accessed through the DIGITS() function before substringing can be done, e.g., SUBSTR(DIGITS(MYDATE), 5, 2).

    The SUBSTR() function converts a numeric value to a string that suppresses leading zeros. The DIGITS() function leaves zeros in place so that SUBSTR() sees leading zeros as characters.

    (Not sure if this is a 2nd post of this comment. The first one simply stopped in the middle of posting and never finished.)

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb

    THANKS Tom

    And I would have gotten away with it if it weren't for those pesky  insignificant zeros

     

    Mydate, Substring(Digits(Mydate, 5, 4)) || substring(Digits(Mydate,3.2)) || substring(Digits(Mydate, 1,2))

      From Myfile

     Where substring(Digits(Mydate,5,2)) in ('19','20')

    49,850 pointsBadges:
    report
  • NicyChn

    Thank you Tom and Philip.

     It worked perfectly.

    110 pointsBadges:
    report
  • philpl1jb
    Thanks for the feedback.
    49,850 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