AS400 combine number fields as date time

[ericfmyers]

I have number fields on the AS400 that I'm trying to combine into a DateTime value. The first field is the date and was able to create the date value using this formula:

stringvar x := totext({EXMP.DATE},0,"");
date(1900+val(left(x,3)),val(mid(x,4,2)),val(right(x,2)));

The second field is the time (hours:minutes:seconds note that the leading zeros are missing on some). A couple of examples in this field are:

94357
131409
120145
51255
164555

When I try to use the timevalue formula it's returning 12:00am for all times. I believe that using this formula doesn't work because of the missing leading zeros:

stringvar x := totext({EXMP.TIME},0,"");
timevalue(val(left(x,6)),val(mid(x,4,2)),val(right(x,2)));


Once those two formulas are working I was going to bundle them using DateTime. Am I going about this the wrong way? Or is there a better solution?

Thank you.

 

[hilfy]

Is val(left(x,6)) a typo? That may be part of your problem.

You'll actually need an if statement here to account for the missing zeroes. Something like this:

If length(x) < 6 then
TimeValue(val(left(x,1)), val(mid(x, 3, 2)), val(right(x,2))
else
TimeValue(val(left(x,2)), val(mid(x, 4, 2)), val(right(x,2))

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[MCuthill]

ericfmyers,

Another way to eliminate the issue with leading zeros is via this formula:
{@Cast_TimeString}

[blue]ToText[/blue]({YourField},"000000",0)

From your post, I assume your date formula works?

To then work these into one DateTime formula, the following should work:
{@Cast_DateTime}

[blue]DateTime[/blue]([blue]Year[/blue]({@YourDateFormula}),[blue]Month[/blue]({@YourDateFormula}),[blue]Day[/blue]({@YourDateFormula}),[blue]Val[/blue]([blue]Left[/blue]({@Cast_TimeString},2)),[blue]Val[/blue]([blue]Mid[/blue]({@Cast_TimeString},3,2)),[blue]Val[/blue]([blue]Right[/blue]({@Cast_TimeString},2)))

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

ericfmyers,

My apologies, I had an "ah-ha"

You can also do the date calculation right in your final formula and save having it separate -- unless you may have a reporting need for the date on it's own. And now that I think of it, you could also remove my first formula by setting the fields to variables (as you have in your original post) and complete this all in one formula.

The following should fulfill your needs (but I no not have Crystal Reports on this PC and able to double check the syntax), and it may require a little fine-tuning.

{@Cast_DateTime_OneStep}

StringVar MyDate := ToText({EXMP.DATE},0,"");
StringVar MyTime := ToText({EXMP.TIME},"000000",0);

[blue]DateTime[/blue](1900+[blue]Val[/blue]([blue]Left[/blue](MyDate,3),[blue]Val[/blue]([blue]Mid[/blue](MyDate,4,2)),[blue]Val[/blue]([blue]Right[/blue](MyDate,2),[blue]Val[/blue]([blue]Left[/blue](MyTime,2)),[blue]Val[/blue]([blue]Mid[/blue](MyTime,3,2)),[blue]Val[/blue]([blue]Right[/blue](MyTime,2)));

Just as a question, I assume your Date field to be structured as follows, YYYMMDD, where the year 2000 is represented by 100? Just an idle curiosity, I am not sure I have seen that standard before.

Hope this helps ericfmyers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ericfmyers]

Mike that worked! Just had to add a couple of parenthesis.

The date field is formatted as YYYMMDD. The first number indicates the century. Anything before 2000 was then just YYMMDD. The main tables were created in the early 80s, and this was the programmer's fix for Y2K. I'm not sure why they only added one digit instead of two. I guess it'll work until Y3K.

Thank you so much for your help.

-Eric

 

[MCuthill]

Excellent!

I looking at this now that it is a sensible time of day, I can certainly see where the parenthesis were missed.

Do you need to look at dates prior to 2000 with this report? You could cast the date into a 7-digit string with the same methodology as we cast the time to place a leading zero for this formula to work for the dates in the 80's and 90's.

Can you post your formula here, so that others can see the final solution?

Cheers! Have a great day!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ericfmyers]

I spoke too soon. I have a few fields that haven't been filled yet. Their value is 0. My work around for now is using these two formulas:

{@CheckDate}
if isnull({EXMP.DATE}) then 1000101
else
if {EXMP.DATE} = 0 then 1000101
else
{EXMP.DATE}

{@CheckTime}
if isnull({EXMP.TIME}) then 101
else
if {EXMP.TIME} = 0 then 101
else
{EXMP.TIME}

I'm not sure if I need the isnull parts but I wanted to make sure it would work. I put those formulas in your code so it doesn't bomb out.

{@Cast_DateTime_OneStep}
StringVar MyDate := ToText({@CheckDate},0,"");
StringVar MyTime := ToText({@CheckTime},"000000",0);

DateTime(1900+Val(Left(MyDate,3)),Val(Mid(MyDate,4,2)),Val(Right(MyDate,2)),Val(Left(MyTime,2)),Val(Mid(MyTime,3,2)),Val(Right(MyTime,2)));


Then I'm suppressing those lines on the report with:

{EXMP.DATE}=0

-Eric

 

[MCuthill]

Thanks for posting the final solution Eric!

Definitely the most straight-forward means to allow for those empty and zero value records.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."