Teradata Date Formats

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 Date FORMAT function is defined as below:

SELECT Date_column  FORMAT '<format_type>'
FROM table_name:

• The date column to be formatted is followed by the FORMAT keyword
• 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 in order to achieve the desired resultant column.

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 in order to achieve the desired resultant column

Furthermore, some of the time 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 in order to achieve the desired resultant column.

Popular Course in this category

All in One Data Science Bundle (360+ Courses, 50+ projects)360+ Online Courses | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (3,220 ratings)

Course Price

View Course

---

Related Courses

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'

Output of this Query:

This will return the order_no along with the order date aliased as oid in the required format. Moreover, as 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 in order 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 .

Output of the above Query will be:

Let’s take one more example to understand how can we modify the format of a date before we insert 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 is 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 dates in Teradata.

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 –

1. Data Analysis Tools Research
2. Data Science Tools
3. Teradata Current Date | How to See?
4. Teradata CASE Statement | How to Use?
5. Teradata Partition by
6. Teradata Architecture | Components
7. Primary Index in Teradata