Date Format Conversion 1

[zupnik]

Hi all,

My Specs:
CR - 8.5
DB - DB2 AS400

The Date (Number Format) I get from the AS400 is: 200,408.19
The way it should display in CR is 081904 as a dateformat.

appreciate all the help

thx

 

[vidru]

Try this formula:

numbervar dt := 200408.19;
totext(Date(Int(dt/100), Int(dt) mod 100, (dt * 100) mod 100), "MMddyy");

-dave

 

[zupnik]

Dave,

thx for the replay

works correct... however

The date field should be 'flexible'.... now it returns 081904 for all dates..

For the AS400 number format 20,040,819.00 I used the following formula:

//If {ART_Consumed_by_reason.IDDLM}=0 then Date(1900,01,01) else


//DATE
//(
//(tonumber((left(totext({ART_Consumed_by_reason.IDDLM}),2)&mid(totext({ART_Consumed_by_reason.IDDLM}),4,2)))),
//(tonumber((mid(totext({ART_Consumed_by_reason.IDDLM}),6,1)&mid(totext({ART_Consumed_by_reason.IDDLM}),8,1)))),
//(tonumber((mid(totext({ART_Consumed_by_reason.IDDLM}),9,2))))
//)

where {..IDDLM} is the date in number format


Q: how does this formula have to look for 200,408.19

thx

 

[vidru]

That was just an example. Replace this line:

numbervar dt := 200408.19;

... with this:

numbervar dt := {ART_Consumed_by_reason.IDDLM};

-dave

 

[zupnik]

Dave,

my bad.... (its so easy just to copy/paste!!)

now, how do I get it to be a date field as opposed to a string??

thx again for your help

 

[bdreed35]

You can remove the Totext function from the second line of Dave's formula:

Date(Int(dt/100), Int(dt) mod 100, (dt * 100) mod 100);


~Brian

 

[vidru]

The only reason I had the ToText in there was because your initial requirement was to display the date in MMDDYY form. I was trying to save you the added step of customizing the display of the resulting Date field.

-dave

 

[zupnik]

Dave & Brian

works like a charm....

appreciate your guys' help

thx

 

[dgillz]

Here's an easier formula you might want to consider:

NumberToDate({YourField}*100)

Then just format the date the way you want it to look.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[zupnik]

dgillz,

NumberToDate is not an acceptable function/opaeration in the version I use.... CR 8.5 It gives me the following error: 'The remaining text does not appear part of the formula'

thx