Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Cool Unanswered: AS400 Date format

    Dear all
    i am using crystal report to design a reports based on AS400 DB2 database, my problem is that the date fields in the AS400 is a numeric field contains the date as the following format : 20040901, do anybody know how to convert this value to date value on my report.

  2. #2
    Join Date
    Nov 2004
    Posts
    8
    DateVar TheDate := Date(0,0,0);
    StringVar TextDate = ToText({as400db2.date});

    if {as400db2.date} != 0 then

    NumberVar yr := ToNumber( TextDate[1 to 4 ] );
    NumberVar mo := ToNumber( TextDate[5 to 6 ] );
    NumberVar dy :=ToNumber( TextDate[7 to 8 ] );

    TheDate := Date( yr, mo, dy );

    endif

    You should also add code to ensure the text is eight characters long and the number values gathered are in a valid range.
    Last edited by twburger; 11-18-04 at 18:55.

  3. #3
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    Hi g_awad, try this. Create a new formula field having the following code
    Code:
    Numbervar y:=0;
    Numbervar m:=0;
    Numbervar da:=0;
    
    y:=Tonumber(left(replace(totext(20040901),",",""),4));
    m:=Tonumber(mid(replace(totext(20040901),",",""),5,2));
    da:=Tonumber(mid(replace(totext(20040901),",",""),7,2));
    
    date(y,m,da)
    Place this formula in the details section. The result is 9/1/2004. Now replace 20040901 by datefiled of your table

    Madhivanan
    Last edited by Madhivanan; 11-19-04 at 05:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •