“Even I don’t’ wake up looking like Cindy Crawford.”
– Cindy Crawford
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.
Format the dates for appearance on the report. The actual data doesn’t change.
Current Date = March 20th, 2013
SELECT Current_Date (FORMAT 'mm-dd-yy') ;
How will the answer appear?
Current Date = March 20th, 2013
SELECT Current_Date (FORMAT 'mm-dd-yy') ;
How will the answer appear?
03-20-13
Current Date = March 20th, 2013
SELECT Current_Date (FORMAT 'mm-dd-yyyy') ;
How will the answer appear?
Current Date = March 20th, 2013
SELECT Current_Date (FORMAT 'mm-dd-yyyy') ;
How will the answer appear?
03-20-2013
Current Date = March 20th, 2013
How will the answer appear?
Current Date = March 20th, 2013
How will the answer appear?
Mar-20-2013
Current Date = March 20th, 2013
How will the answer appear?
Current Date = March 20th, 2013
How will the answer appear?
March-20-2013
When you use mmmm with the format statement you get the month spelled out.
Current Date = March 20th, 2013
How will the answer appear?
Current Date = March 20th, 2013
How will the answer appear?
When you use ddd with the format statement you get the Julian date.
Current Date = March 20th, 2013
How will the answers appear?
Current Date = March 20th, 2013
SELECT Current_Date (FORMAT 'eee-mm-ddd-yyyy') ;
Sun-03-20-13
Sunday-03-20-13
When you use eee with the format statement you get the first three letters of the month. When you use eeee you get the entire month spelled out..
Current Date = March 20th, 2013
SELECT Current_Date (FORMAT 'eeeeBBbbMMMM'),
How will the answer appear?
Current Date = March 20th, 2013
How will the answer appear?
Sunday March
You can also format how numbers appear.
Such as in the case of a phone number.
SELECT 5133000346 (FORMAT '999-999-9999'),
How will the answer appear?
By putting in 999-999-9999, this is telling the system to put the literal numbers 5133000346 next to the SELECT into the formatting style.
You can also format how numbers appear.
Such as in the case of a phone number.
SELECT 5133000346 (FORMAT '999-999-9999'),
How will the answer appear?
513-300-0346
By putting in 999-999-9999, this is telling the system to put the literal numbers 5133000346 next to the SELECT into the formatting style.
Notice that we’ve taken out one of the 9s in the Format Statement
SELECT '5133000346' (FORMAT '99-999-9999'),
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.
Notice that we’ve taken out one of the 9s in the Format Statement
SELECT '5133000346' (FORMAT '99-999-9999'),
How will the answer appear?
************
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.
You can also format letters and words!
SELECT 'ABCDE' (FORMAT 'XxX'),
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.
You can also format letters and words!
SELECT 'ABCDE' (FORMAT 'XxX'),
How will the answer appear?
ABC
You can also FORMAT characters. Look at this example. It doesn’t matter if the X’s are capitalized or not.
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.
SELECT 1021.53 (FORMAT 'ZZZZZZ9.99'),
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.
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.
SELECT 1021.53 (FORMAT 'ZZZZZZ9.99'),
How will the answer appear?
1021.53
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.
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.
SELECT 1021.53 (FORMAT
'99999999.9999'),
How will the answer appear?
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.
SELECT 1021.53 (FORMAT
'99999999.9999'),
How will the answer appear?
00001021.5300
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.
SELECT 1021.53 (FORMAT '$$$$$$9.99'),
How will the answer appear?
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.
SELECT 1021.53 (FORMAT '$$$$$$9.99'),
How will the answer appear?
$1021.53
You can also use commas in your Formatting statements.
SELECT 1021.53 (FORMAT '$,$$$,$$9.99'),
How will the answer appear?
You can also use commas in your Formatting statements.
SELECT 1021.53 (FORMAT '$,$$$,$$9.99'),
How will the answer appear?
$1,021.53
You can also use commas in your Formatting statements.
SELECT 0.53 (FORMAT '$,$$$,$$9.99'),
How will the answer appear?
You can also use commas in your Formatting statements.
SELECT 0.53 (FORMAT '$,$$$,$$9.99'),
How will the answer appear?
$0.53
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.
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.
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 ;
SELECT
,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 for display.
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 ;
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.
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.
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.
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.
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.