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

 

So far, what I've done has been to isolate the Year, Month and Day, and convert them to text, and then concatenate them back 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 to two digits, but when the value is less than ten, I only get one. 

 

So, my question is two fold...

 

First, how can I force the leading zero when the value of the month or day is a single digit? 

 

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

 

 

Here is 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,"")

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

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

 

and...

 

Numeric Date:  Tonumber( mailto:%7b@Text - {@Text mailto:Year%7d&%7b@Text - Year}&{@Text mailto:Month%7d&%7b@Text - Month}&{@Text Day})

(the "ToNumber" command does cause a decimal, but I just format the report to suppress decimals)

 

Additional Confussion...

My difficulty is compounded by my own ignorance... that is, I am not sure why it matters that the field is formatted as a number.  The report is essentially going to serve as the Check Date, in a check register for our new parent company.   They have us on a system called Positive Pay, which requires that we submit this check register to them with the date in this format, in a numeric field, 8 characters long.  Since this report will ultimately be submitted as either an xls, xlsx, or csv file, couldn't I just leave it as text when I export it to excel?  That is, won't excel just recognize it as a number anyway?

 

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

 

Thanks,

 

Paul

tonumber(totext({aphistvouch.distdate},'yyyyMMdd'))

 

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