Chapter 20
Format Functions
“Even I don't’ wake up looking like Cindy Crawford.”
Table of Contents Chapter 20 – Format Functions
– The Basics of the FORMAT Command
– Quiz – How will the Date Appear after Formatting
– Answer to Quiz – How will the Date Appear after Formatting
– Quiz – How will the Date Appear after Formatting
– Answer to Quiz – How will the Date Appear after Formatting
– Formatting with MMM for the Abbreviated Month
– Answer to Quiz – How will the Date Appear after Formatting
– Formatting with MMMM for the Full Month Name
– Formatting with MMMM for the Full Month
– Formatting with DDD for the Julian Day
– Formatting with DDD for the Julian Day
– Formatting with EEE or EEEE for the Day of the Week
– EEEE for the Abbreviated or Full Day of the Week
– Placing Spaces inside your Formatting Commands with a B
– Formatting Spaces with B or b
– Troubleshooting when Formatted Data Overflows
– Troubleshooting when Formatted Data Overflows
– Formatting with X or x Results
– Formatting with $ and Commas
– Formatting with $ and Commas Visual
– Formatting with $ and Commas and 9
– Formatting with $ and Commas and 9 with Zero Dollars
– A Great Formatting Example for Day, Month and Year
– A Trick to get SQL Assistant to Format Data
– Using the CASESPECIFIC (CS) Command in Teradata Mode
– Using NOT CASESPECIFIC (CS) in ANSI Mode
The FORMAT Command
SELECT Current_Date (FORMAT ‘mm-dd-yy’) ;
SELECT Current_Date (FORMAT ‘mm-dd-yy’) ;
In this example, we are using it for dates. All dates in Teradata are stored in the systems as an INTERGERDATE. This allows it to be read and formatted easily.
The Basics of the FORMAT Command
Format the dates for appearance on the report. The actual data doesn't change.
Quiz – How will the Date Appear after Formatting
Current Date = March 20th, 2011
SELECT Current_Date (FORMAT ‘mm-dd-yy’) ;
How will the answer appear?
Answer to Quiz – How will the Date Appear after Formatting
Current Date = March 20th, 2011
SELECT Current_Date (FORMAT ‘mm-dd-yy’) ;
How will the answer appear?
ANSWER: 03-20-11
Quiz – How will the Date Appear after Formatting
Current Date = March 20th, 2011
SELECT CurrentyDate (FORMAT ‘mm-dd-yyyy’) ;
How will the answer appear?
Answer to Quiz – How will the Date Appear after Formatting
Current Date = March 20th, 2011
SELECT Current_Date (FORMAT ‘mm-dd-yyyy’) ;
How will the answer appear?
ANSWER: 03-20-2011
Formatting with MMM for the Abbreviated Month
Current Date = March 20th, 2011
How will the answer appear?
Answer to Quiz – How will the Date Appear after Formatting
Current Date = March 20th, 2011
How will the answer appear?
You get the first three letters of the month!
ANSWER: Mar-20-2011
Formatting with MMMM for the Full Month Name
Current Date = March 20th, 2011
How will the answer appear?
Formatting with MMMM for the Full Month
Current Date = March 20th, 2011
How will the answer appear?
ANSWER: March-20-2011
Formatting with DDD for the Julian Day
Current Date = March 20th, 2011
How will the answer appear?
Formatting with DDD for the Julian Day
Current Date = March 20th, 2011
How will the answer appear?
Formatting with EEE or EEEE for the Day of the Week
Current Date = March 20th, 2011
How will the answers appear?
EEEE for the Abbreviated or Full Day of the Week
Current Date = March 20th, 2011
Placing Spaces inside your Formatting Commands with a B
Current Date = March 20th, 2011
SELECT Current_Date (FORMAT ‘eeeeBBbbMMMM’);
How will the answer appear?
Formatting Spaces with B or b
Current Date = March 20th, 2011
How will the answer appear?
ANSWER: Sunday March
Formatting with 9
You can also format how numbers appear. Such as in the case of a phone number.
How will the answer appear?
By putting in 999-999-9999, this is telling the system to put the literal numbers 5133000341 next to the SELECT into the formatting style.
Formatting with 9 Results
You can also format how numbers appear. Such as in the case of a phone number.
By putting in 999-999-9999, this is telling the system to put the literal numbers 5133000341 next to the SELECT into the formatting style.
Troubleshooting when Formatted Data Overflows
Notice that we've taken out one of the 9s in the Format Statement
How will the answer appear?
The FORMAT OVERFLOW is what happens when your system doesn't have enough spaces in the FORMAT to cover the number you are trying to format.
Troubleshooting when Formatted Data Overflows
Notice that we've taken out one of the 9s in the Format Statement
This is not an error, but something is wrong!
The FORMAT OVERFLOW is what happens when your system doesn't have enough spaces in the FORMAT to cover the number you are trying to format.
Formatting with X or x
You can also format letters and words!
How will the answer appear?
You can also FORMAT characters. Look at this example. It doesn't matter if the X's are capitalized or not.
Formatting with X or x Results
You can also format letters and words!
You can also FORMAT characters. Look at this example. It doesn't matter if the X's are capitalized or not.
Formatting with Z
The Z's represent potential data. This tells the system that if there is a number to put in the Z's position, then put it in. If there is not, leave it blank.
How will the answer appear?
What a ‘9’ represents in a format statement is that if there is a number in the ‘9’ position, then put it in. If there isn't one, then you put a blank.
Formatting with Z Visual
The Z's represent potential data. This tells the system that if there is a number to put in the Z's position, then put it in. If there is not, leave it blank.
What a ‘9’ represents in a format statement is that if there is a number in the ‘9’ position, then put it in. If there isn't one, then you put a blank.
Formatting with 9
The 9's represent potential data. This tells the system that if there is a number to put in the 9's position, then put it in. If there is not, leave it blank.
How will the answer appear?
Formatting with 9 Visual
The 9's represent potential data. This tells the system that if there is a number to put in the 9's position, then put it in. If there is not, leave it blank.
Formatting with $
What the $ allows you to do is to tell your formatting to place a $ sign in front of the result set, but only at the beginning.
How will the answer appear?
Formatting with $ Visual
What the $ allows you to do is to tell your formatting to place a $ sign in front of the result set, but only at the beginning.
Formatting with $ and Commas You can also use commas in your Formatting statements.
How will the answer appear?
Formatting with $ and Commas Visual
You can also use commas in your Formatting statements
Formatting with $ and Commas and 9
You can also use commas in your Formatting statements.
How will the answer appear?
Formatting with $ and Commas and 9 with Zero Dollars
You can also use commas in your Formatting statements.
The ‘9' sees the 0 and knows to bring it back in the answer set. The floating $ will only bring back a $ for the first character.
A Great Formatting Example
SELECT ‘ABCDE' (FORMAT ‘XxX’) AS Fmt_Shorter
,2014859999 (FORMAT ‘999-999-9999’) AS Fmt_Phone
,1021.53 (FORMAT ‘ZZZZZZ9.9999’) AS Z_Press
,991001(date) (FORMAT ‘Yyddd’) AS Fmt_Julian
,991001(date) (FORMAT ‘eee’) As Weekday
,991001 (FORMAT ‘$$$$,$$$.99’) AS Fmt_Pay ;
There are only two things that need to be watched when using the FORMAT function. First, the data type must match the formatting character used or a syntax error is returned. So, if the data is numeric, use a numeric formatting character and the same condition for character data. The other concern is configuring the format mask big enough for the largest data column. If the mask is too short, the SQL command executes, however, the output contains a series of ************* to indicate a format overflow.
A Great Formatting Example for Day, Month and Year
SELECT Current_Date (FORMAT 'mm-dd-yy') as Digit2_YR
,Current_Date (FORMAT 'mm-dd-yyyy') as Digit4_YR
,Current_Date (FORMAT 'mmm-dd-yyyy')as Mnth_Initials
,Current_Date (FORMAT 'mmmm-dd-yy')as Mnth_Spelled
,Current_Date (FORMAT 'mm-ddd-yyyy') as Day_Julian
,Current_Date (FORMAT 'eeeBmm-dd-yy') as Day_of_Week
,Current_Date (FORMAT 'eeeeBmm-dd-yy') as Day_Spelled
All of these FORMAT requests work wonderfully if the client software is BTEQ or the Nexus Query Chameleon, but SQL Assistant has problems with formatting. After all, it is a report writer and these are report writer options. The issue is that the ODBC and SQL Assistant look at the data as data, not as a report. Since many of the formatting symbols are “characters” they cannot be numeric. Therefore, the ODBC strips off the symbols and presents the numeric data to the client software for display.
A Trick to get SQL Assistant to Format Data
SELECT CAST( (4859999 (FORMAT ‘999-9999’)) AS CHAR(8) ) AS Phone
,991001(date) (FORMAT ‘yyyy.mm.dd’) (CHAR(10) ) AS Cast_Date
,CAST( (991001 (FORMAT ‘$$$$,$$$.99’)) AS CHAR(11) ) AS Pay ;
| Phone | Cast_Date | Pay |
| 485-9999 | 1999.10.01 | $991,001.00 |
If a tool uses the ODBC, the FORMAT in the SELECT is ignored and the data comes back as data, not as a formatted field. This is especially noticeable with numeric data and dates.
To force tools like SQL Assistant to format the data, the software must be tricked into thinking the data is character type, which it leaves alone. This can be done using the CAST function. The Nexus Query Chameleon does not have a problem formatting.
Using the CASESPECIFIC (CS) Command in Teradata Mode
In Teradata Mode a capital ‘A’ is seen as the same in comparison as a little ‘a’. The CASESPECIFIC command, which is abbreviated as CS will make sure the case of a letter is examined.
Using NOT CASESPECIFIC (CS) in ANSI Mode
In ANSI Mode a capital ‘A’ is seen as different in comparison as a little ‘a’. The NOT CASESPECIFIC command, which is abbreviated as NOT CS will make sure the case of a letter is examined and returned whether or not they have the same case.
Using the LOWER Command
In ANSI Mode a capital ‘A’ is seen as different in comparison as a little ‘a’. The LOWER command can be used to make sure the case of a column is lowered.
Using the UPPER Command
In ANSI Mode a capital ‘A’ is seen as different in comparison as a little ‘a’. The UPPER command can be used to make sure the case of a column is UPPERED.