EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Teradata Tutorial Teradata Date Formats

Teradata Date Formats

By Priya PedamkarPriya Pedamkar

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

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:

Teradata Date Formats Example 2

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:

Teradata Date Formats Example 1

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

We hope that this EDUCBA information on “Teradata Date Formats” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  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
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more