15.00 - Format for a CASE Expression - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Format for a CASE Expression

Default Format

The result of a CASE expression is displayed using the default format for the resulting data type. The result of a CASE expression does not apply the explicit format that may be defined for a column appearing in a THEN/ELSE expression.

Consider the following table definition:

   CREATE TABLE duration
      (i INTEGER
      ,start_date DATE FORMAT 'EEEEBMMMBDD,BYYYY'
      ,end_date DATE FORMAT 'DDBM3BY4' );

Assume the default format for the DATE data type is 'YY/MM/DD'.

The following query displays the result of the CASE expression using the 'YY/MM/DD' default DATE format, not the format defined for the start_date or end_date columns:

   SELECT i, CASE
              WHEN i=1 
              THEN start_date
              WHEN i=2 
              THEN end_date
             END
   FROM duration
   ORDER BY 1;

Using Explicit Type Conversion to Change Format

To modify the format of the result of a CASE expression, use CAST and specify the FORMAT clause.

Here is an example that uses CAST to change the format of the result of the CASE expression in the previous query:

   SELECT i, ( CAST ((CASE
                WHEN i=1 
                THEN start_date
                WHEN i=2 
                THEN end_date
               END) AS DATE FORMAT 'M4BDD,BYYYY'))
   FROM duration
   ORDER BY 1;

For information on the default data type formats and the FORMAT phrase, see SQL Data Types and Literals.