Formatting a numeric field

[Neville]

I have a numeric field that is on a MSSQL backend. It is used to save the date in the format 20150601.
I would like to format it on the client side to read 01 June 2015 or 01/06/2015.
How and where do I go about it.

Many thanks

[lgrupido]

Re: Formatting a numeric field

OK - I have to ask - WHY are you storing the date this way?

Do you want to format it for viewing only or do you need to edit it too?

If it is view only, your best bet would be to create a MSSQL view over the table and use an MSSQL function to covert the number to a date, then display the date, which will have more formatting options for you.

If you need to edit it, then I would recommend saving it as a date instead of as a numeric field.

If you HAVE to have the numeric version, you might be best off putting in two fields. (A date and a copy of the date as the number) Do all your editing and viewing from the date field, and keep the other field updated with a trigger or with javascript.

Personally, whatever route you go, I think you might be better off if the field was a character field, not a numeric field. It's not like you can do any math with it. Whatever you want to do with it, you will have to convert it to a character or date field to work with it.

If you were storing the JULIAN Date, it might be different - but you are not doing that either.

[Davidk]

Re: Formatting a numeric field

There are 2 excellent new methods Alpha has added to help format stuff... thank goodness... because dates are horrible to work with.
In your case, I believe you need to coerce the data a bit.

Here we are getting the "date" value from a List control, Detail View. Since we know the format of the date is yyyyMMdd... and we need to make it more date like... so just split it up and shove in some forward slashes. I tried the methods using the date as it, but the .fromFormat method didn't take it.

Then we use Alpha's .fromFormat to convert the value into a real date object. And now that it's a real date object we can out put it any way we want.

var sqlDate = {dialog.Object}.getValue('TestDate');
sqlDate = sqlDate.substr(0,4) + "/" + sqlDate.substr(4,2) + "/" + sqlDate.substr(6,4);

var d1 = new Date();
d1.fromFormat(sqlDate,'yyyy/MM/dd');

var dString1 = d1.toFormat('dd MONTH yyyy');
var dString2 = d1.toFormat('dd/MM/yyyy');

{dialog.Object}.setValue('txtDate1',dString1,false);
{dialog.Object}.setValue('txtDate2',dString2,false);

https://s3-us-west-2.amazonaws.com/a...DateFormat.PNG

[Neville]

Re: Formatting a numeric field

Many thanks guys.
Larry just to point out that I do not have control over this database as this is how it is used in the AccPac accounting system. I am drawing the data from there. Yes if I designed it I would use a date field.
Thanks David....will look into it.

Best Regards