Numeric Date to True Date 1

[3dthies]

I want to convert a numeric field from AS/400 into a date field. the field format is for example "20021005" (2002=year, 10=month, 05=day)

Normaly i will do it with the following formula:

WhilePrintingRecords;
NumberVar input:= {PGNB01.DCRT01};

Date ( Val ( ToText (input,0,"&quot [1 to 4]),
Val (ToText (input,0,"&quot [5 to 6]),
Val (ToText (input,0,"&quot [7 to 8]))

The problem is that the numeric fiel in the AS/400 has different datas:

"20021005" (2002=year, 10=month, 05=day) or
"021005" (02=year 2002, 10=month, 05=day) or
"1005" (nothing=year 2000, 10=month, 05=day) or
"505" (nothing=year 2000, 5=month 05, 05=day)
"691005" (69=year 1969, 10=month, 05=day)

to solve the problem i have to break down the numeric data i think but i have no idea to do it!!

Has anybody some input for me?

Thanks very much

 

[Naith]

Go to

http://support.crystaldecisions.com/downloads

and download the NumberToDate function. You'll find it under ufltdate.exe).

Then, all you have to do is NumberToDate({PGNB01.DCRT01}), and that's it.

Naith

 

[3dthies]

thank you naith

but are you sure that function "knows" that a numerical field e.g. "1005" is year 2000 month 10 and day 5 or "691005" is year 1969 month 10 and day 5?

Thies

 

[Naith]

Very good call, Thies. I'm not sure how I managed to miss that nasty caveat.

One question though; there doesn't seem to be a reason why you appear to be representing single integer months as n and single integer days as nn. Is this consistent? Or can you have 0501 as well as 501?

The following solution is based on the assumption that the answer to the above question is that it is consistent.

WhilePrintingRecords;
StringVar strinput := ToText({PGNB01.DCRT01},0,'');

If Length(strinput) >= 6
    Then CDate(Val(Left(strinput,4)),Val(Mid(strinput,5,2)),Val(Right(strinput,2)))
//If you downloaded NumberToDate, you can use
//"Then NumberToDate({PGNB01.DCRT01})"
//instead of the previous line of code
Else
If Length(strinput) = 5
Then
    If ToNumber(Mid(strinput,1,2)) > 50
    Then CDate(Val('19'+Mid(strinput,1,2)),Val(Mid(strinput,3,1)),Val(Right(strinput,2)))
    Else CDate(Val('20'+Mid(strinput,1,2)),Val(Mid(strinput,3,1)),Val(Right(strinput,2)))
Else
If Length(strinput) = 4
Then
    CDate(2000,Val(Left(strinput,2)),Val(Right(strinput,2)))
Else
If Length(strinput) = 3
    Then
    CDate(2000,Val(Left(strinput,1)),Val(Right(strinput,2)))

Naith

 

[3dthies]

Hi Naith,

i can have 0501 as well 501 (month/day) or
20020501 (year/month/day) or 020501 (year/month/day)

 

[Naith]

The formula will still work for all those formats. I asked the question before I created the formula, and then just forgot to delete it.

Naith

 

[3dthies]

Many thanx

Thies

 

[3dthies]

I have tested the AS/400 database with your formula the whole night and it works fine to 90% but there are special field entry's which don't work with the formula.

Examples:

Database Date (year/month/day)
19691005 = 1969/10/05
691005 = 1969/10/05
00691005 = 1969/10/05
19 691005 (with space
but it's not string)= 1969/10/05
0505 = 2000/05/05
505 = 2000/05/05
0000505 = 2000/05/05
20010505 = 2001/05/05
011005 = 2001/10/05

Sure, it's a very bad programmed database!!!

Have you an idea? Many thx

Thies

 

[Naith]

I am concerned with how bad your data is.

It seems to me that you should attempt to get your data cleansed before you report on it, rather than try to capture every conceivable development in this field.

In the meantime, however, the following formula addresses all of your afore mentioned cases:

WhilePrintingRecords;
StringVar strinput := ToText({PGNB01.DCRT01},0,'');

If Left(strinput,2) = '00'
Then strinput := Mid(strinput,3)
Else strinput := strinput;

strinput := Replace(strinput,' ','');

If Length(strinput) = 8
    Then CDate(Val(Left(strinput,4)),Val(Mid(strinput,5,2)),Val(Right(strinput,2)))
Else
If Length(strinput) = 6
Then 
    If ToNumber(Mid(strinput,1,2)) > 50
    Then CDate(Val('19'+Mid(strinput,1,2)),Val(Mid(strinput,3,2)),Val(Right(strinput,2)))
    Else CDate(Val('20'+Mid(strinput,1,2)),Val(Mid(strinput,3,2)),Val(Right(strinput,2)))
Else
If Length(strinput) = 5
Then
    If ToNumber(Mid(strinput,1,2)) > 50
    Then CDate(Val('19'+Mid(strinput,1,2)),Val(Mid(strinput,3,1)),Val(Right(strinput,2)))
    Else CDate(Val('20'+Mid(strinput,1,2)),Val(Mid(strinput,3,1)),Val(Right(strinput,2)))
Else
If Length(strinput) = 4
Then
    CDate(2000,Val(Left(strinput,2)),Val(Right(strinput,2)))
Else
If Length(strinput) = 3
    Then
    CDate(2000,Val(Left(strinput,1)),Val(Right(strinput,2)))

If you have instances where dates can be 7 characters, or longer than 8 characters, I don't know how they should be addressed, so this formula hasn't attempted to.

Naith