Date Conversion Formats. TSQL.

Hi Everyone,

Hope this conversion table will help to present date and time in proper way.

For example if you wish to see the current date, in MM/DD/YY please use 1 as a format:

Statement:

SELECT CONVERT(varchar(120),GETDATE(),1)

Will return you 12/28/17

In general, using SELECT CONVERT(varchar(120),GETDATE(),FORMAT)

Where FORMAT is just a number from the list will provide you respective output:

output                   style
Apr 28 2014  9:31AM          0
04/28/14                     1
14.04.28                     2
28/04/14                     3
28.04.14                     4
28-04-14                     5
28 Apr 14                    6
Apr 28, 14                   7
09:31:28                     8
Apr 28 2014  9:31:28:580AM   9
04-28-14                     10
14/04/28                     11
140428                       12
28 Apr 2014 09:31:28:580     13
09:31:28:580                 14
2014-04-28 09:31:28          20
2014-04-28 09:31:28.580      21
04/28/14  9:31:28 AM         22
2014-04-28                   23
09:31:28                     24
2014-04-28 09:31:28.580      25
Apr 28 2014  9:31AM          100
04/28/2014                   101
2014.04.28                   102
28/04/2014                   103
28.04.2014                   104
28-04-2014                   105
28 Apr 2014                  106
Apr 28, 2014                 107
09:31:28                     108
Apr 28 2014  9:31:28:580AM   109
04-28-2014                   110
2014/04/28                   111
20140428                     112
28 Apr 2014 09:31:28:580     113
09:31:28:580                 114
2014-04-28 09:31:28          120
2014-04-28 09:31:28.580      121
2014-04-28T09:31:28.580      126
2014-04-28T09:31:28.580      127
28 جمادى الثانية 1435  9:31:28:580AM    130
28/06/1435  9:31:28:580AM    131

All the best,
Sergey.

Amount to Words conversion at Acumatica Reports

Hi Everyone,

As we do not yet have Amount to Words function inside our top notch Report Designer, let me share one created to print cheques from AR module.

Same logic applies and can be reused anywhere inside Acumatica Report Designer. I did it for up to 10,000 amount, but you can easily extend it upwards.

1. First of all lets create few Variables on top of the Reporting Group where we are going to use A2W conversion.

a) AmountCents - that will be total amount converted to Cents. I used ARPayment.CuryOrigDocAmt but you can use ANY other numeric field as a SOURCE
b) AmtThousands - that will be just how many thousands we have
c) AmtHundreds - displays number of hundreds only
d) AmtOnes - Ones only
c) AmtCents - Cents only

Below are all the formulae:

AmountCents:
=CInt([ARPayment.CuryOrigDocAmt]*100)

AmtThousands:
=Floor($AmountCents/100000)

AmtHundreds:
=Floor(($AmountCents-$AmtThousands*100000)/10000)

AmtTens:
=Floor(($AmountCents-$AmtThousands*100000-$AmtHundreds*10000)/1000)

AmtOnes:
=Floor(($AmountCents-$AmtThousands*100000-$AmtHundreds*10000-$AmtTens*1000)/100)

AmtCents:
=CInt(Right(CStr($AmountCents),2))

2. Secondly add quite long formula into the field itself, here it is:

Your Field: =Iif($AmtThousands<>0,Switch($AmtThousands=10,'Ten',$AmtThousands=9,'Nine',$AmtThousands=8,'Eight',$AmtThousands=7,'Seven',$AmtThousands=6,'Six',$AmtThousands=5,'Five',$AmtThousands=4,'Four',$AmtThousands=3,'Three',$AmtThousands=2,'Two',$AmtThousands=1,'One') + ' Thousand ','') + Iif($AmtHundreds<>0,Switch($AmtHundreds=9,'Nine',$AmtHundreds=8,'Eight',$AmtHundreds=7,'Seven',$AmtHundreds=6,'Six',$AmtHundreds=5,'Five',$AmtHundreds=4,'Four',$AmtHundreds=3,'Three',$AmtHundreds=2,'Two',$AmtHundreds=1,'One') + ' Hundred ','') + Iif($AmtTens<>0,Switch($AmtTens=9,'Ninety',$AmtTens=8,'Eighty',$AmtTens=7,'Seventy',$AmtTens=6,'Sixty',$AmtTens=5,'Fifty',$AmtTens=4,'Forty',$AmtTens=3,'Thirty',$AmtTens=2,'Twenty',$AmtTens=1,'') + ' ','') + Iif($AmtTens=1,Switch($AmtOnes=9,'Nineteen',$AmtOnes=8,'Eighteen',$AmtOnes=7,'Seventeen',$AmtOnes=6,'Sixteen',$AmtOnes=5,'Fifteen',$AmtOnes=4,'Fourteen',$AmtOnes=3,'Thirteen',$AmtOnes=2,'Twelve',$AmtOnes=1,'Eleven',$AmtOnes=0,'Ten') + ' ','') + Iif($AmtTens<>1,Switch($AmtOnes=9,'Nine',$AmtOnes=8,'Eight',$AmtOnes=7,'Seven',$AmtOnes=6,'Six',$AmtOnes=5,'Five',$AmtOnes=4,'Four',$AmtOnes=3,'Three',$AmtOnes=2,'Two',$AmtOnes=1,'One',$AmtOnes=0,''),'') + Iif($AmountCents=$AmtCents,'Zero','') + Iif($AmtCents=0,' Only',' and ' + CStr($AmtCents) + '¢ Only')

3. Let see what we got:

It works only up to 10k, but if you wish, you can add in front of the formula further parsing.

All the Best,
Sergey.