Dates, times, and timestamps in Teradata (V2R5.1) can be a little tricky. This document explains
how to do various things that you may want to do.
Preliminaries
The examples below use Teradata syntax, so they assume that you are running in BTEQ. If you are
using SQL Assistant (Queryman), be sure to uncheck the option box
"Allow use of ODBC SQL Extensions in queries". Changing the Date Time Format in your ODBC data
source to AAA or IIA may also help.
datecol means a column defined as DATE.
Defining columns and formats
Times and timestamps can be defined with any number of decimal places from 0 to 6 (time(0),
timestamp(6), etc.). As it turns out, however, MPRAS on Intel doesn't keep track of anything
beyond 2 decimal places (hundredths of seconds), so in many cases it is useless to define columns
with more than 2 decimal places. If, however, you are loading data from another server, that data
could have digits down to the microsecond.
The formats for all columns are described in the
SQL Reference: Data Types and Literals;, Chapter 8. In V2R5, you can specify
many different formats for time and timestamp fields.
Dates
Literals
Date literals can be specified in many ways:
 in the same format as the column they are being compared to
where infamy_date = '12/07/1941'
 with an explicit format
where infamy_date = '07Dec1941' (date, format 'DDMMMYYYY')
 in ANSI standard form (must be preceded by the keyword DATE)
where infamy_date = date '19411207'
 in numeric form (not recommended)
where infamy_date = 411207
Observe in the examples above that the word DATE must be used to specify the data type. To get the system date, therefore,
CURRENT_DATE is a better choice than DATE.
Number of days between two dates
datecol  datecol
will return the number of days between two dates.
select date '20030815'  date '20030101';
(2003081520030101)

226
Adding or subtracting months
Generally speaking, you should use the ADD_MONTHS function to add months to a date (or to subtract
months). Your project may require adding a number of days, but if calendar months are required,
ADD_MONTHS is the way to go.
select add_months(current_date, 3);
ADD_MONTHS(Date, 3)

20051019
select add_months(current_date, 2);
ADD_MONTHS(Date, 2)

20050519
/*** Last day of the month is still the last day ***/
select add_months(date '20020131', 1);
ADD_MONTHS(20020131, 1)

20020228
Computing the day of the week
Computing the day of the week for a given date is not easy in SQL. If you need a weekday, I recommend
that you look it up in the view sys_calendar.calendar (or join to it), thus:
select day_of_week
from sys_calendar.calendar
where calendar_date = date '20030501';
day_of_week

5 [i.e. Thursday]
Computing the first day of a month
select datecol  extract(day from datecol) + 1
This subtracts the number of days since the beginning of the month, taking you to "day 0",
or the day before the first of the month; then adds 1.
Computing the last day of a month
select add_months((datecol  extract(day from datecol)+1),1)1
Same idea, but this computes the first day of the following month and then subtracts 1.
Times
Literals
The easiest way to specify a time literal is in ANSI form with a 24hour clock:
time '15:09:17'
But in V2R5 you can use another format as long as you tell Teradata what it is:
'080917PM' (time, format 'HHMISST')
Changing floats to times
Some Data Dictionary tables have time columns that are defined as FLOAT rather than TIME. Here's how to
convert them to TIMEs, believe it or not:
select cast(cast(cast(TimeFld as format '99:99:99.99')
as char(11)) as time(6))
from DBC.EventLog ...
Time differences
First we shall address the case where your time data is defined as a number (FLOAT or DECIMAL, perhaps)
in hhmmss form.
select ((time02 / 10000) * 3600 +
(time02 / 100 MOD 100) * 60 +
(time02 MOD 100)) 
((time01 / 10000) * 3600 +
(time01 / 100 MOD 100) * 60 +
(time01 MOD 100)) as time_diff
from dttest2;
time_diff

6432. [in seconds  about 1.8 hours]
If the earlier time could fall on one day and the later time on the next day, you may have to
add 86,400 (the number of seconds in one day) to the later time, like so:
select case
when time02 >= time01 then
((time02 / 10000) * 3600 +
(time02 / 100 MOD 100) * 60 +
(time02 MOD 100)) 
((time01 / 10000) * 3600 +
(time01 / 100 MOD 100) * 60 +
(time01 MOD 100))
else /*** Midnight has passed ***/
(((time02 / 10000) * 3600 +
(time02 / 100 MOD 100) * 60 +
(time02 MOD 100)) + 86400) 
((time01 / 10000) * 3600 +
(time01 / 100 MOD 100) * 60 +
(time01 MOD 100))
end as time_diff
from dttest2;
time_diff

18094.
Next we consider the case where your time data is defined as TIME(n). The usual way to take
the difference of two times would be as follows:
select time02  time01 hour(2) to second
from dttest3;
(time02  time01) HOUR TO SECOND

10:39:23.000000
The above result has a data type of INTERVAL. If, however, you want to compute the difference in
seconds, as above, you again have to split the times up:
select (extract(hour from time02) * 3600 +
extract(minute from time02) * 60 +
extract(second from time02)) 
(extract(hour from time01) * 3600 +
extract(minute from time01) * 60 +
extract(second from time01)) as time_diff
from dttest3;
time_diff

38363
/*** After midnight ... ***/
select case
when time02 >= time01 then
(extract(hour from time02) * 3600 +
extract(minute from time02) * 60 +
extract(second from time02)) 
(extract(hour from time01) * 3600 +
extract(minute from time01) * 60 +
extract(second from time01))
else
(extract(hour from time02) * 3600 +
extract(minute from time02) * 60 +
extract(second from time02) + 86400) 
(extract(hour from time01) * 3600 +
extract(minute from time01) * 60 +
extract(second from time01))
end as time_diff
from dttest3;
time_diff

38363
Timestamps
Literals
The easiest way to specify a timestamp literal is in ANSI form:
timestamp '19941225 23:46:29'
But in V2R5 you can use another format as long as you tell Teradata what it is:
'12/25/1994 11:46:29PM' (timestamp, format 'MM/DD/YYYYBHH:MI:SST')
Extracting the date or time portion of a timestamp
Extract the date or time portion of a timestamp thus:
select cast(ts01 as date) from dttest;
ts01

04/07/27
select cast(ts01 as time(6)) from dttest;
ts01

10:24:37.739920
Combining date and time to make a timestamp
Thanks to Dieter Noeth for this tip. The most efficient way to combine a date and a time is to cast
the date to a timestamp and add the time. But you can't simply add a time, because TIME is a point in
time and not a duration. So you have to transform it into an interval:
cast(Date_column as TimeStamp(6))
+ ((Time_column  time '00:00:00') hour to second(6))
Length of time between two timestamps
You can subtract one timestamp from another. The result will be an interval, and you must specify
a precision for the interval, like so:
select ts02  ts01 day(4) to second(6)
from dttest;
(ts02  ts01) DAY TO SECOND

371 05:26:35.639649
select ts01  ts02 day(4) to second(6)
from dttest;
(ts01  ts02) DAY TO SECOND

371 05:26:35.639649
You can also convert this interval to seconds or minutes like so:
/*** Difference in seconds ***/
select (ts02  ts01 day(4) to second) as tsdiff,
(extract(day from tsdiff) * 86400)
+ (extract(hour from tsdiff) * 3600)
+ (extract(minute from tsdiff) * 60)
+ extract(second from tsdiff) as sec_diff
from dttest;
tsdiff sec_diff
 
371 05:26:35.639649 32073995.639649
/*** Difference in minutes ***/
select (ts02  ts01 day(4) to minute) as tsdiff,
(extract(day from tsdiff) * 1440)
+ (extract(hour from tsdiff) * 60)
+ extract(minute from tsdiff) as min_diff
from dttest;
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
tsdiff min_diff
 
371 05:26 534566
Number of days between two timestamps
If you just want the number of days between two timestamps and wish to ignore the time
portion, either of the following two techniques will work, but note the differences. The
first technique lops off the time portion of each timestamp, so it will be equivalent to
subtracting the two days; the result is an integer. The second will take the time portion
into account and return an interval, so it will not count periods of time less than 24 hours.
Thus, the result could be one less than with the first technique. If you use the second
technique, be sure to allow enough digits for DAY.
sel cast(ts02 as date)  cast(ts01 as date)
from dttest;
(ts02ts01)

371  [type of this result is INTEGER]
sel ts02  ts01 day(4)
from dttest;
(ts02  ts01) DAY

371  [type of this result is INTERVAL DAY]
Other operations on timestamps
Operand 1 
Operator 
Operand 2 
Result Type 
Timestamp 
+ or  
Interval 
Timestamp 
Interval 
+ 
Timestamp 
Timestamp 
Interval 
+ or  
Interval 
Interval 
Interval 
* or / 
Numeric 
Interval 
Numeric 
* 
Interval 
Interval 
References
Teradata Database SQL Reference: Data Types and Literals,
B0351143083A (Nov. 2003).
Information Technology — Database Languages — SQLPart 2: Foundation,
ISO/IEC 90752:2003, esp. Subclause 4.6.
