Teradata Timestamp/Date Formatting

Pradeep

Teradata Timestamp/Date Formatting

Teradata provides a way to display timestamps and dates in the desired format. Various character and character strings (Metacharacters) are available in Teradata which can be used along with FORMAT keyword to display timestamp/date in the required format.

Below tables show most of the metacharacters that can used with FORMAT keyword.

MetaCharacters	Description / Functionality
B	Display white space in formated timestamp
YYYY	Display year in four digits(example: 1987)
YY	Display year in two digits(example: 87)
MM	Display month of the year in number format(example: 12)
M3 or MMM	Display month in 3 characters format(example: Jun)
M4 or MMMM	Display full month name(example: June)
DD	Display day of the month in two digits(example: 30)
D3 or DDD	Display day of the year(example: 276)
HH	Display hour of the time in two digits(example: 17)
MI	Display minutes of the time in two digits(example: 59)
SS	Display seconds of the time in two digits(example: 58)
E3 or EEE	Display day of the week in 3 characters(example: Wed)
E4 or EEEE	Display full name of week day(example: Wednesday)
T	Display time in AM/PM format
: (Colon)	Other permitted character in format
- (Hyphen)	Other permitted character in format
/ (Backslash)	Other permitted character in format
. (Period)	Other permitted character in format

Note: IDE/JDBC Tools may not show formatted timestamp properly if output is not specifically casted to VARCHAR/CHAR.

All the below examples(after removing time formating part) can be used with DATE columns as well

Example 1: Display timestamp in YYYY/MM/DD HH:MI:SS format

BTEQ SYNTAX: SELECT  CURRENT_TIMESTAMP (format 'YYYY/MM/DDbHH:MI:SS') Formated_timestamp;
OUTPUT: 2018/10/03 12:49:53


IDE/Tool SYNTAX: SELECT  CAST((CURRENT_TIMESTAMP (format 'YYYY/MM/DDbHH:MI:SS')) AS VARCHAR(50)) Formated_timestamp;

Example 2: Display timestamp in YY/MM/DD HH:MI:SS format

BTEQ SYNTAX: SELECT  CURRENT_TIMESTAMP (format 'YY/MM/DDbHH:MI:SS') Formated_timestamp;
OUTPUT: 18/10/03 13:26:22


IDE/Tool SYNTAX: SELECT  CAST((CURRENT_TIMESTAMP (format 'YY/MM/DDbHH:MI:SS')) AS VARCHAR(50)) Formated_timestamp;

Example 3: Display timestamp in YYYY/MM/DD HH:MI:SS format with AM/PM

BTEQ SYNTAX: SELECT  CURRENT_TIMESTAMP (format 'YYYY/MM/DDbHH:MI:SSBT') Formated_timestamp;
OUTPUT: 2018/10/03 12:50:46 PM


IDE/Tool SYNTAX: SELECT CAST(CAST(CURRENT_TIMESTAMP as  FORMAT 'M4bDD,YYYYbHH:MI:SSBT') AS VARCHAR(50)) Formated_timestamp;

Example 4: Display timestamp in YYYY-MM-DD HH:MI:SS format with millisecond or microsecond up to 1 digit. One (1) can be replaced with any number upto 6 to get that many number of microsecond.

BTEQ SYNTAX: SELECT  CURRENT_TIMESTAMP (format 'YYYY-MM-DDbHH:MI:SS.s(1)') Formated_timestamp;
OUTPUT: 2018-10-03 12:52:23.2


IDE/Tool SYNTAX: SELECT  CAST((CURRENT_TIMESTAMP (format 'YYYY-MM-DDbHH:MI:SS.s(1)')) AS VARCHAR(50)) Formated_timestamp;

Example 5: Display timestamp in YYYY-MM-DD HH:MI:SS format with millisecond or microsecond up to 5 digit. Five (5) can be replaced with any number upto 6 to get that many number of microsecond.

BTEQ SYNTAX: SELECT CURRENT_TIMESTAMP (format 'YYYY-MM-DDbHH:MI:SS.s(5)') Formated_timestamp;
OUTPUT: 2018-10-03 12:52:32.32000


IDE/Tool SYNTAX: SELECT CAST((CURRENT_TIMESTAMP (format 'YYYY-MM-DDbHH:MI:SS.s(5)')) AS VARCHAR(50)) Formated_timestamp;

Example 6: Display formatted timestamp with first 3 characters of month name.

BTEQ SYNTAX: SELECT CAST(CURRENT_TIMESTAMP as  FORMAT 'M3bDD,YYYYbHH:MI:SS') Formated_timestamp;
OUTPUT: Oct 03,2018 12:05:32


IDE/Tool SYNTAX: SELECT CAST(CAST(CURRENT_TIMESTAMP as  FORMAT 'M3bDD,YYYYbHH:MI:SS') AS VARCHAR(50)) Formated_timestamp;

Example 7: Display formatted timestamp with complete name of the month.

BTEQ SYNTAX: SELECT CAST(CURRENT_TIMESTAMP as  FORMAT 'M4bDD,YYYYbHH:MI:SS') Formated_timestamp;
OUTPUT: October 03,2018 11:42:58


IDE/Tool SYNTAX: SELECT CAST(CAST(CURRENT_TIMESTAMP as  FORMAT 'M4bDD,YYYYbHH:MI:SS') AS VARCHAR(50)) Formated_timestamp;

Example 8: Display formatted timestamp with first 3 characters of weekday name.

BTEQ SYNTAX: SELECT CAST(CURRENT_TIMESTAMP as  FORMAT 'E3bM4bDD,YYYYbHH:MI:SS') Formated_timestamp;
OUTPUT: Wed October 03,2018 12:05:40


IDE/Tool SYNTAX: SELECT CAST(CAST(CURRENT_TIMESTAMP as  FORMAT 'E3bM4bDD,YYYYbHH:MI:SS') AS VARCHAR(50)) Formated_timestamp;

Example 9: Display formatted timestamp with complete name of the weekday.

BTEQ SYNTAX: SELECT CAST(CURRENT_TIMESTAMP as  FORMAT 'E4bM4bDD,YYYYbHH:MI:SS') Formated_timestamp;
OUTPUT: Wednesday October 03,2018 12:05:47


IDE/Tool SYNTAX: SELECT CAST(CAST(CURRENT_TIMESTAMP as  FORMAT 'E4bM4bDD,YYYYbHH:MI:SS') AS VARCHAR(50)) Formated_timestamp;

Example 10: Display day of year instead of day of month.

SELECT CAST((CURRENT_TIMESTAMP  (FORMAT 'DDD')) AS VARCHAR(20))||' day of '||CAST((CURRENT_TIMESTAMP  (FORMAT 'YYYY')) AS VARCHAR(20))Formated_timestamp;
OUTPUT: 276 day of 2018

Example 11: Display given timestamp as custom formatted string.

SELECT 'Today is '||CAST((CURRENT_TIMESTAMP  (FORMAT 'E4,bDD')) AS VARCHAR(20))||' day of '||CAST((CURRENT_TIMESTAMP  (FORMAT 'M4,YYYY')) AS VARCHAR(20))|| ' and time is '||CAST((CURRENT_TIMESTAMP  (FORMAT 'HH:MI:SS')) AS VARCHAR(20))  Formated_timestamp;
OUTPUT: Today is Wednesday, 03 day of October,2018 and time is 12:22:51