This post summarize the common functions that are used when dealing with DateTime datatypes in Teradata.

Converting String to DateTime

String to Date

SELECT CAST('2017-10-15' AS DATE FORMAT 'YYYY-MM-DD');
SELECT CAST('2017 10 15' AS DATE FORMAT 'YYYYBMMBDD');
SELECT CAST('2017 Oct 15' AS DATE FORMAT 'YYYYBMMMBDD');
SELECT CAST('15, Oct, 2017' AS DATE FORMAT 'DD,BMMM,BYYYY');
-- default ansi format
SELECT CAST('2017-10-15' AS DATE);
-- use to_date function
SELECT to_date('2017-10-15', 'YYYY-MM-DD');

String to Time

-- no seconds
SELECT CAST('16h20m' AS TIME(0) FORMAT 'HHhMIm');
-- with seconds and space
SELECT CAST('16h 20m 32s' AS TIME(0) FORMAT 'HHhBMImBSSs');
  -- with miliseconds
  SELECT CAST('16:20:32.2456 ' AS TIME(6));
  -- With PM or AM
  SELECT CAST('PM 04:20:32.245 ' AS TIME FORMAT 'TBHH:MI:SS.S(4)B');
  SELECT CAST('AM 04:20:32.245 ' AS TIME FORMAT 'TBHH:MI:SS.S(4)B');

String to Timestamp

SELECT CAST('2017-10-15 23:59:59.999999' AS TIMESTAMP(6) WITH TIME ZONE);
-- with time zone Z
SELECT CAST('2017-10-15 23:59:59.999999 +10:00' AS TIMESTAMP(6) WITH TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ');
-- time zone in the string
SELECT CAST('2017-10-15 23:59:59 +10:00' AS TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI:SS.S(6)BZ');

String to Interval

SELECT CAST('-114-25' AS INTERVAL YEAR(4) TO MONTH);
SELECT CAST('-9999-11' AS INTERVAL YEAR(4) TO MONTH);
SELECT CAST('9999-11' AS INTERVAL YEAR(4) TO MONTH);

Converting DateTime to String

Conversions

-- using cast
SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(100));
SELECT CAST(CURRENT_DATE AS VARCHAR(100));
-- using functions
SELECT to_char(CURRENT_DATE,'YYYY-MM-DD');
SELECT to_char(CURRENT_TIMESTAMP,'MON-DD-YYYY" "HH:MI:SS" "TZH:TZM');
-- time examples
SELECT CAST(TIME AS VARCHAR(100));
SELECT CAST(CURRENT_TIME AS VARCHAR(100));
SELECT to_char(CURRENT_TIME,'HH:MI:SS" "TZH:TZM');

Extract Components

SELECT EXTRACT(YEAR
     FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(MONTH
     FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(DAY
     FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(MINUTE
     FROM TIME);
SELECT EXTRACT(HOUR
     FROM TIME);
SELECT EXTRACT(SECOND
     FROM TIME);

-- extract from interval
SELECT EXTRACT(DAY
     FROM CAST('99' AS INTERVAL DAY(2)));

DateTime Comparison and Operations

Add Intervals

-- add 10 year and 1 month to the current timestamp
SELECT CURRENT_TIMESTAMP + CAST('10-01' AS INTERVAL YEAR(2) TO MONTH);
-- minus 10 years from the current date
SELECT DATE + CAST('-10-00' AS INTERVAL YEAR(2) TO MONTH);
SELECT ADD_MONTHS(DATE, -12*10);

DateTime Comparison

-- Timestamp minus timestamp get one interval
SELECT CURRENT_TIMESTAMP - ADD_MONTHS(CURRENT_TIMESTAMP, -133) YEAR(4) TO MONTH;
SELECT DATE - (DATE + CAST('99' AS INTERVAL DAY(2))) YEAR(4) TO MONTH;

info Last modified by Raymond at 3 years ago * This page is subject to Site terms.

More from Kontext

local_offer teradata local_offer python

visibility 150
thumb_up 0
access_time 26 days ago

Pandas is commonly used by Python users to perform data operations. In many scenarios, the results need to be saved to a storage like Teradata. This article shows you how to do that easily using JayDeBeApi or  ...

open_in_new View open_in_new Spark + PySpark

local_offer teradata local_offer SQL

visibility 26
thumb_up 0
access_time 2 months ago

In SQL Server, we can use TRUNCATE statement to clear all the records in a table and it usually performs better compared with DELETE statements as no transaction log for each individual row deletion. The syntax looks like the following: TRUNCATE TABLE { database_name.schema_name.tab...

open_in_new View open_in_new Code snippets

local_offer teradata local_offer python local_offer Java

visibility 106
thumb_up 0
access_time 2 months ago

Python JayDeBeApi module allows you to connect from Python to Teradata databases using Java JDBC drivers. In article Connect to Teradata database through Python , I showed ho...

open_in_new View open_in_new Python Programming

local_offer teradata local_offer SQL

visibility 112
thumb_up 0
access_time 2 months ago

OREPLACE functions in Teradata can be used to replace or remove characters from a string. OREPACE is Teradata's extension to ASNI SQL. The usual REPLACE function is not available. ANSI SQL REPLACE function REPLACE function is commonly implemented in many other SQL databases such as ...

open_in_new View open_in_new Code snippets

info About author

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward