(FORMAT (value,'picture-format'))
Prints picture formats to a variable, rather than only to the screen. You can use FORMAT anywhere that you can use a function. The result of the FORMAT function is always text.
In the syntax for this function, value is the value you want to be displayed in a particular format; it can be a column, variable, or a constant value. 'Picture-format' is the picture format you establish.
The FORMAT function can be useful in several ways:
•Aligning decimals
•Capturing date and time using system variables
•Formatting currency
•Formatting text
•Punctuating long numbers
The characters you can use to format your data are listed below.
For All Data |
|
[<] |
Data is left justified. |
[>] |
Data is right justified. |
[^] |
Data is centered. |
For Numbers |
|
[-] |
Places a minus sign to the right of a negative number. |
[DB] |
Places DB to the right of a negative number. |
[( )] |
Encloses a negative number in parentheses. |
[CR] |
Places CR to the right of a positive number. |
9 |
Fills unused space with blanks. |
0 |
Fills unused space with zeros. |
* |
Fills unused space with asterisks. |
For Text |
|
_ |
Letters are uppercase; other characters are blank. |
| |
Letters are lowercase; other characters are blank. |
% |
Letters are uppercase; other characters are unchanged. |
? |
Letters are lowercase; other characters are unchanged. |
For Dates |
|
MMDDYYYY |
Displays the month, day, and year |
MM |
Displays the month |
DD |
Displays the day |
YYYY |
Displays the year |
WWW |
Displays the day name, with a 3-letter abbreviation |
WWW+ |
Displays the full name for the day of the week |
MMM |
Displays the month name, with a 3-letter abbreviation |
MMM+ |
Displays the full name for the month |
CC |
Displays the century, AD or BC |
any combination |
Any combination of the month, day and year can be used. |
For Times |
|
HHMMSS |
Displays the hour, minute, and second |
HH |
Displays the hour |
MM |
Displays the minute |
SS |
Displays the second |
.SSS |
Displays thousandths of a second |
AP |
Displays AM or PM when using a 12-hour format |
any combination |
Any combination of the hours, minutes, and seconds can be used. |
NN |
Displays minutes (when capturing date and time using #NOW) |
Samples:
Aligning Decimals
The following example shows how you can use the FORMAT function to align decimal points in a column:
SELECT (FORMAT(bonuspct,'99.000')) FROM salesbonus
The following example shows the effect of the FORMAT function on the above SELECT statement:
Using FORMAT |
Without FORMAT |
0.003 |
0.003 |
0.002 |
0.002 |
0.000 |
0 |
0.001 |
0.001 |
Formatting Currency
The following example shows how you can use the FORMAT function to only display whole dollars:
SELECT (FORMAT(netamount,'[>]$999,999')) FROM salesbonus
This SELECT statement displays data as right justified whole dollars, as shown below:
Using FORMAT |
Without FORMAT |
$176,000 |
$176,000.00 |
$87,500 |
$87,000.00 |
Formatting Text
You must include a format character for each text character. The following example shows how you can use the FORMAT function to display text in uppercase:
SELECT (FORMAT(empfname,'________')) FROM employee
Using FORMAT |
Without FORMAT |
JUNE |
June |
ERNEST |
Ernest |
PETER |
Peter |
Punctuating Long Numbers
The following example shows how you can use the FORMAT function to include a comma after the thousand's place:
SELECT (FORMAT(transid,'999,999')) FROM transmaster
The following shows the effect of the FORMAT function on the above SELECT statement:
Using FORMAT |
Without FORMAT |
104 |
104 |
2,002 |
2002 |
39,765 |
39765 |
Capturing Date and Time Using System Variables
Here's a simple routine to create a unique file name by capturing the date and time using R:BASE system variables:
-- Example 01: (Using .#DATE and .#TIME as two separate variables)
CLS
CLEAR VARIABLES vDateTxt,vTimeTxt,vFileName
SET VAR vDateTxt TEXT = NULL
SET VAR vTimeTxt TEXT = NULL
SET VAR vFileName TEXT = NULL
SET VAR vDateTxt = (FORMAT(.#DATE,'MMDDYYYY'))
SET VAR vTimeTxt = (FORMAT(.#TIME,'HHMM'))
SET VAR vFileName = +
((CVAL('DATABASE'))+'_'+.vDateTxt+'_'+.vTimeTxt+'.BKP')
CLEAR VARIABLES vDateTxt,vTimeTxt
RETURN
-- vFileName will return the text variable as:
-- RRBYW20_12302020_1630.BKP
-- Database Name, Date and Time will vary on your end
-- Example 02: (Using .#DATE and .#TIME as one variable in expression)
CLS
CLEAR VARIABLES vFileName
SET VAR vFileName TEXT = NULL
SET VAR vFileName = +
((CVAL('DATABASE'))+'_'+(FORMAT(.#DATE,'MMDDYYYY'))+ +
'_'+(FORMAT(.#TIME,'HHMM'))+'.BKP')
RETURN
-- vFileName will return the text variable as:
-- RRBYW20_12302020_1630.BKP
-- Database Name, Date and Time will vary on your end
-- Example 03: (Using .#NOW) - Short and Swift
CLS
CLEAR VARIABLES vFileName
SET VAR vFileName TEXT = NULL
SET VAR vFileName = +
((CVAL('DATABASE'))+''-'+(FORMAT(.#NOW,'MMDDYYYY_HHNN'))+'.BKP')
RETURN
-- Notice the "NN" for minutes when using .#NOW (not a typo)
-- vFileName will return the text variable as:
-- RRBYW20_12302020_1630.BKP
-- Database Name, Date and Time will vary on your end
Now that you have successfully created a unique file name, you may use the following routine to make a backup of your live database, on demand! Here's how:
OUTPUT .vFileName
UNLOAD ALL
OUTPUT SCREEN
The resulting two files will be created in the same folder, unless a different folder name is specified:
•RRBYW20_12302020_1630.BKP
•RRBYW20_12302020_1630.LOB
