When you want to change the display of a default output, you need to use the function FORMAT. Remember this function only changes the display and does not truncates data in reality. This is one of the reasons it is widely used for reporting purpose. The maximum string length a FORMAT function can return is 30 characters. Syntax to know the format of an existing column using FORMAT function is;

FORMAT (column name); i.e.

SELECT FORMAT (date)

To change the display of a default output;

SELECT <column name> (format ‘pattern’)

For example to change the default output of the current date;

SELECT date (format ‘MMMDDYYYY’);

Other Symbols used for formatting (character) data;

 X – Used for single representation of a character data.

9 – Used for single representation of an integer data (from 0-9 with “.” for decimal).

E- Used for Exponentials.

G- Used for single logical representation of a graphical data (images and languages).

Symbols used for special character formatting;

($ , . – / %) – Used for fixing any of the character of the similar nature, like $ for Dollar sign, “,” for Comma and “.” for decimal etc.

Symbols for DATE formatting

M – Used for Month display in case of date function. Single or double Ms are for digit display and three Ms (‘MMM’) are for character display of the month.

D – To display the day in a month in the date function.

Y – To display year in a date function. Note that a lower case of all the symbols above can also be used to get the same result. One example;

SELECT ‘MYFORMAT’ (FORMAT ‘XXxx’) as CharFormat,

4466466444 (FORMAT ’999-999-9999′) as PHONE,

13245.55 (FORMAT ’99999.999′) as DecimalFormat,

124445 (FORMAT ‘$$$,$$$$.99′) as DollarFormat

If you are using Teradata SQL Assistant, you might not see all the formatting displayed. USE BTEQ instead to see proper output.

Our Random Articles

More Links