Introduction to Teradata Date Formats
Date FORMAT function is utilized to updated the default date format as per the requirements. By default, For ANSI dates, Teradata follows the date format as YYYY-MM-DD, whereas for the integer dates, the default date format is YY/MM/DD. A FORMAT function simply overrides the Date format of the session user and the system. On the other hand, your system admin can change the default format that gets applied to the date for the integer data types. This can be achieved by modifying the date element in a custom specification for the data formatting SDF file. This info is then made readily useable by the Teradata system by using the tdlocaledef utility.
The primary syntax of the Date FORMAT function is defined as below:
SELECT Date_column FORMAT '<format_type>'
FROM table_name:
- The FORMAT keyword follows the date column to be formatted.
- This format keyword is followed by the actual date format in which we want the date_column to be formatted as per the requirements.
There are numerous date formats that can be utilized as required. Some of them are mentioned below:
These formats are utilized in place of ‘<format_type>’ in the above syntax to achieve the desired resultant column.
| FORMAT Phrase | Result |
| FORMAT ‘YY/MM/DD’ | 20/01/02 |
| FORMAT ‘DD-MM-YY’ | 02/01/2020 |
| FORMAT ‘YYYY/MM/DD’ | 2020/01/02 |
| FORMAT ‘YYYY-MM-DD’ | 1985-12-29 |
| FORMAT ‘YYYY.DDD’ | 2020.002 |
| FORMAT ‘YYBDDD’ | 20 002 |
| FORMAT ‘DDBMMMBYYYY’ | 02 Jan 20 |
| FORMAT ‘MMMBDD, BYYYY’ | Jan 02, 2020 |
| FORMAT ‘YYYYBMMMBDD’ | 12020 Jan 02 |
| FORMAT ‘MMM’ | Jan |
| FORMAT ‘EEE,BM4BDD,BYYYY’ | Thu, January 02, 2020 |
| FORMAT ‘E4,BMMMMBDD,BYYYY’ | Thursday, January 02, 2020 |
| FORMAT ‘E4BDDBM4BYYYY’ | Jeudi 02 Janvier 1985 |
| (Jeudi is French for Thursday, and Janvier is French for January.) | |
| FORMAT ‘999999’ | 200102 |
Whereas some of the timestamp formats are mentioned down below, which can be utilized to modify the timestamp columns. These formats are utilized in place of ‘<format_type>’ in the above syntax to achieve the desired resultant column
| FORMAT Phrase | Result |
| FORMAT ‘MM/DD/YYBHH:MIBT’ | 02/01/20 01:30 PM |
| FORMAT ‘MMMBDD, BYYBHH:MI:SS’ | Jan 02, 20 13:30:00 |
| FORMAT ‘E3,BM4BDD,BY4BHH:MI:SSDS(F)’ | Thu, January 02, 2020, 13:30:00.00 |
| FORMAT ‘YYYY-MM-DDBHH:MI:SSDS(F)Z’ | 2020-01-02 13:00:00.64+03:00 |
Furthermore, some of the time formats are mentioned below, which can modify the timestamp columns. These formats are utilized in place of ‘<format_type>’ in the above syntax to achieve the desired resultant column.
4.7 (3,220 ratings)
View Course
| FORMAT Phrase | Result |
| FORMAT ‘HH:MIBT’ | 01:30 PM |
| FORMAT ‘HH:MI’ | 13:23 |
| FORMAT ‘HH.MI.SS’ | 13.30.00 |
| FORMAT ‘HH:MI:SSBT’ | 01:30:00 Nachm
(Nachm is German for PM.) |
| FORMAT ‘HH:MI:SSDS(F)’ | 13:30:00.00 |
| FORMAT ‘HH:MI:SSDS(F)Z’ | 13:00:00.00+03:00 |
| FORMAT ‘HHhMImSSs’ | 13h20m53s |
The format types need to be consistent. If in case we want to modify the date format on the go within the select statement itself, then these formats need to be consistent, as mentioned above. Otherwise, we may not get the expected results.
How to Use Teradata Date Format Function?
Let’s take an example to understand the usage in detail:
For example, Let’s suppose we want to filter the orders from the order table having a specific date only, and the format needs to be:
MMM DD, YYYY
Then we can utilize the FORMAT function in the where clause itself to filter the same.
Code:
SELECT order_no, order_date FORMAT 'MMMbDD,bYYYY' as oid
from order_table
where oid = 'MAR 26, 2020'
The output of this Query:
This will return the order_no and the order date aliased as oid in the required format. Moreover, we have incorporated the where clause to return only the order date equivalent to MAR 26, 2020.
That’s why we can see only the orders made on that date only as of the result of this query:
How do We Compare the Dates having Different Formats?
If a filed needs to be inserted in an existing date column, then the format of that particular entry must be either the format of the data that is being held by that column or the ANSI Date literal format.
If we want to compare a field with a date value, then the format must match one of the below mentioned:
- Format of the date of that column.
- ANSI literal format.
- The default DATE data type format in the SDF & DATE format determined from the DateForm.
For instance, the comparison works fine if in case the data is CHAR(8) in the form YY/MM/DD and a DATE column with which we want to compare it to is of the format, YY/MM/DD. Obviously, the comparison fails if the column to be compared to has the format as YYYY-MM-DD. Moreover, To perform date-specific comparisons that do not meet these guidelines, It is suggested to convert the values as described in SQL Functions, Expressions, Operators, & Predicates.
Let’s discuss some examples to have a better understanding of how the FORMAT function works with dates.
Example:
SELECT ORDER_DATE (FORMAT 'MMMbdd,bYYYY') (CHAR(12), UC);
Suppose the date used for this data is Using 1985-09-12.
The output of the above query will be:
Let’s take one more example to understand how we can modify the date’s format before inserting the same into a table as per the field/column of that particular table.
INSERT t1 (TIME '11:45:25.123-08:00', TIMESTAMP '2000-12-10 11:45:25.1234');
Here the Time and timestamp are modified before it gets inserted into the table t1.
Conclusion
- FORMAT function is used to modify the format of Dates, Time, Timestamp in Teradata.
- Format_type based on what we want to convert the format of the date, time or timestamp, we pass the format_type after the FORMAT function in the SQL.
- There are numerous formats that can be specified as per the requirements to modify the format of Teradata dates.
Recommended Article
This is a guide to Teradata Date Formats. Here we discuss the Introduction and how to use Teradata Date Format Function along with examples. You can also go through our other suggested articles to learn more –
