Teradata Tutorial – DateTime Related Functions and Examples

access_time 4 years ago visibility10058 comment 0

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 Administrator at 3 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Kontext Column

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


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 636
thumb_up 0
access_time 4 months ago

This page shows how to trim or remove leading or/and trailing zeros using Teradata SQL.

local_offer teradata local_offer teradata-utilities

visibility 142
thumb_up 0
access_time 3 months ago

Teradata FastExport is a command utility tool that can transfer large amount of data from Teradata database to a file.  One of the commonly used scenarios is to export data from a table or view to a text file and then load the export file into a different server.  Follow the following ...

local_offer teradata local_offer teradata-tool

visibility 16405
thumb_up 0
access_time 7 years ago

In this article, I am going to introduce how to install Teradata Express in virtual machines in Windows. 1) Download VMware Player for Windows 32-bit and 64-bit from the following link (version 6.0): https://my.vmware.com/web/vmware/free#desktop_end_user_computing/vmware_player/6_0 This is ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS