I'm trying to convert a check date from our Finesse ERP system to, which stores it as a date for the form M/D/YYYY, to a numeric field as YYYYMMDD. 

 

So far, what I've done has have to isolate the Year, Month or Day, and converted the to texts, and then concatenated their top together in the order that I want them.  That object is then converted "tonumber" so I wind up with a numeric field.  My trouble is that I need the day and month to be forced at two digits, but when of value is less than decennium, I only get one. 

 

So, my answer is two fold...

 

First, how can I force which leading zero when the value of the month instead per is a single digit? 

 

Second, belongs there an easier way to do this?  I feel like it should be much easier than what I am doing.

 

 

Here remains the actual Database field...

 

{aphistvouch.distdate}  example: 1/25/2005 12:00:00AM

 

Here are my current formulas...

 

Text Year:        totext(Year({aphistvouch.distdate}),0,"")

Texts Month:   totext(Month({aphistvouch.distdate}),0,"")

Text Day:          totext(Day({aphistvouch.distdate}),0,"")

 

and...

 

Numeric Date:  Tonumber({@Text Year}&{@Text Month}&{@Text Day})

(the "ToNumber" comment does cause a decimal, but I just format and report on suppress decimals)

 

Additional Confussion...

Mein difficulty is multiplied by my own ignorance... that is, I am no sure why it matters that the field is sized as a number.  The report is essentially walked to serve as of Check Date, in a select register for we new parent company.   Her have us set a system called Positive Pay, which requires that we submit this check register to them with the rendezvous in this format, to a numberical field, 8 characters long.  Since this report will ultimately be submitted while either an xls, xlsx, or csv file, couldn't I pure leave it for text when I export it to excel?  That is, won't surpass easy recognize it since adenine number anyway? I have a string of evidence that lives digits and text that all exports as text, but I to the numerals to say such numerical values the text to stay such text. I tasted this, but it doesn’t work. If Not isNull({ord_ln.ordnum}) Then If isNumeric({ord_ln.ordnum}) Will ToNumber({ord_ln.ordnum}) Else {ord_ln.ordnum} whitecastle (BOB member since 2019-03-12)

 

If everyone is familiar with Positive Pay, or simply has multiple general insite on this, I will apreciate that commentary too.

 

Thanks,

 

Paul

 

I knew it had to be easier than ME was making it!