0 - 50 of 51 tags for timestamp

I need to query a teradata table by referencing a column that is of type Timestamp(6). I'm not successful at getting the right format so far. What do I need to set the Size, Precision, and Scale values to for this to work. (By size, precision, and scale I mean these parameter values in Visual Studio's Dataset Editor)

Hi all, it's my firt post, I hope you can help me here. Within the same day for one ID I have many overlapping events. Basically each event is added on top of scheduled events instead of overwriting existing records. Here is example:

There is a requirement like this:
A column from source whose value is UTC millisecond, for example: 1439374041
Then does anyone know, in Teradata, is there any function can convert UTC millisecond to Timestamp (6)?
1439374041   ->   2015-08-12 10:07:21.000000

I have time in format HH:MM and date in format mm/dd/yyyy. I want COL1 with time formated as HH:MM:SS and COL2 a combination of COL1 and date in format MM/DD/YYYY HH:MM:SS.
Also, then I need to subtract COL3 (format mm/dd/yyy hh:mm:ss) from COL2.
How can I do this?

Hi all,
I have some problem about the query which finds the data between 2013-12-31 and today
I took invalid timestamp error.
The query I used looks like this:
select * from table1 where the_date between '2013-12-31' and current_time
Please help me!

I'm super new to TD and SQL. This code was given to by a co-worker. It works but very slow. Is there a way to speed it up  using 'Interval' and 'Timestamps'?



CASE WHEN TEMP1>=240000 

Hello, I'm looking at clinic data and I'd like to group my check-in times into three main categories: 1) 7am-4pm, 2) 4:01pm-6pm, and 3) 6:01pm-11:59pm. Check-in time is formatted as follows: mm/dd/yyyy hh:mm:ss, and is 24-hour. But I reformatted it like this: hh:mi:ssbt. So now it gives me a 12-hour time with an AM/PM designation.

I am trying to write a macro because I need to run this monthly. I have to run this monthly for different groups of potential, which I want to pass by parameters.
In the past, I used the current_date, and it worked, but now I would like to pass the data as a parameter and use that one, but that doesn't work:

Hi ,
When i migrate Date/Timestamp/Time Data from SQL Server to Teradata , I can see 6 hours of difference among the dates where teradata date/Timestamp is behind . Please help me on this .
Thanks in advance !!

Hi All,
I have a requirement which goes like this:
My timestamp(6) field, which is, say, myTimeValue contains: 2014-10-08 10:14:51.210000

I am facing an issue while trying to insert a null value in a timestamp column.
Please follow the below step to get the error: [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.

In our database we have a system-generated ID that is built off of the timestamp. It’s not a date or time format, it’s a string, like (20130405103422), which is essentially the year, month, day, hour, minute and second that the record was created. I want to join it to another field, which is date, but I need to convert it first. How do I do that

My customer has loaded a TD test table with a date & time column that looks like this:
Column:      XXXXXX   varchar(23) 
7/31/2014 9\:20\:03 AM
8/4/2014 8\:23\:15 AM
12/6/2014 2\:31\:07 PM 
What is the quickest/easiest way to convert the data to TIMESTAMP(6),  if possible at all ?   

I was suggested by DBA to change in one of tables , the partitioning column from timestamp to date.
In explain plan I can see that in both cases partition prunning takes place.
Is there anybody that is master in partitioning in teradata and can help if there is some logical reasoning for such change ?

my timestamps are in this format 1405720077410
Wondering if anyone knows how to correctly convert it to date? I have tried below but day is off by one day
cast((cast(cast(700101 as date) +  eventtime_
 / 86400 as timestamp(6)) +
     ( eventtime_
 mod 86400) * interval '00:00:01' hour to second ) as timestamp(6)) date_

  Can we compress Timestamp field in TD14 (I know that Timestamp fields for only NULL values can be compressed in earlier versions). If yes, can the syntax also be provided?
Thanks for the help

Hi all,
I am writing CLIv2 program (acutally this will be external procedure later) and have following question. When issuing following query "SELECT current_timestamp(0)" in the program to Teradata the result row is represented in the CHAR format YYYY-MM-DD HH:MI:SS. Is it possible to make CLIv2 return TimeStamp data type, I mean:

Hi Experts,
I have the below query in whcih i need to compare timestamp value 'YYYY-MM-DDBHH:MI:SS.S(6)  with
a date value in format   '01/23/2014 11:53:50.000000' as below , but i am getting error as invalid timestamp

Running the following query:

SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime

 Returns the result of:

Dear All,
I have a Table where i have 2 fields - date_col and time_col.
I am trying to select the min(time_col) for a particular date / current_date...
When i select all the records from the table for a particular date, i can see the min of time_col is '00:01:33'

I run a number of simple programs in SQL Assistant that insert rows into tables for predictive modeling (I'm a user, not a DBA).  Before each insert I make a copy of the target table as a backup.   The backup table's name is the target table's name with a "_bu" suffix.

This is my first post in this forum. I am new to teradata DB. There is a project that I am working on where we are receiving the date in varchar format :

I need to load a file using mload where  the load clm has a data like this 03/29/2013 12:00:00 PM.
select cast(cast('03/29/2013 12:00:00 PM' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SSBT') as varchar(22))
I need it to load as Timestamp rather than varchar

My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
12/12/1912 00:00:00
tried with the current_timestamp(0)
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
12/12/1912 00:00:00
tried with the current_timestamp(0)
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

I have a timestamp with time zone field and I would like to get the number of seconds that have elapsed from the start of that day.  This seems like it should be very trivial but I've searched for hours and tried many things that don't work.  What is a simple method for getting the number of seconds?

I have timestamp values in Varchar field. Trying to populate this data in a table where field types are properly defined, and I am unable to push data from table a (timestamp is in varchar) to table b (where I want the date to be in timestamp field). Getting error message 2666. How can I find out fields with bad dates only?

I am bumping into timestamp conversion error when loading to timestamp column with time zone. The table in the column define as 
​snippet of fastload:


        device_dt_ut (VARCHAR(32)),



Hi all,

In my blog entries "Teradata Columnar" and "9.2 Quintillion? What's that all about?", I discussed column partitioning and the increased partition limit that are introduced in TD 14.0.  But there are other TD 14.0 partitioning enhancements that you may find useful.  The following provides a brief introduction to these enhancements; for more detailed information, see the Orange Book: Increased Partition Limit and other Partitioning Enhancements, the Orange Book: Teradata Columnar, and the TD 14.0 manuals.

estimated cardinality for the following predicate towards timestamp column is far too high vs date column

1) Table DDL:


Tables that are dropped and reloaded daily, how can I determine if the table has been today's load, not yesterday's?

The Teradata Temporal feature, available with Teradata 13.10 allows the customer to capture, track, and analyze the full history of evolving business data rather than just the most current updates.



I'm very much new to Teradata and writting queries as such. I have a CHAR Type column which contains data as follows

'2012-01-20 02:32:37.000000'.


How do I convert this CHAR type data to DATE Type, preferably as a timestamp?






I just needed to get the difference between 2 Timestamps in minutes and couldn't find any useful way on the net, so I decided to add my own solution here, in case someone else needs something similar.



I am sometimes encountering a syntax error (3706) when using CASE with an IN clause, see (1) below.  The error is happening with timestamp columns and sometimes with varchar's (when the individual varchar values in the IN are concatenated, ie. col1 || col2).  Any info on this?

I am trying to call Stored Procedure with parameters. Procedure have two TIMESTAMP parameters for example use below one:

Teradata Database 14.0's new partition limit of 9223372036854775807 (over 9 quintillion) is coming!

Hi All,

Can you please explain the concept for timestamp(0),timestamp(1) ....timestamp(6)..?

Thanks in advance for the help.

Is it possible to get the timestamp to display the timezone like this using SQL?
2011-04-08 09:16:10 PM Central Standard Time

What I have so far:

current_timestamp as myDefaultTimestamp
, cast(cast(current_timestamp AS TIMESTAMP(6)
FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z') as varchar(40)) as myTimeZone
, cast(
cast(current_timestamp AS TIMESTAMP(6)
FORMAT 'E3,BM4BDD,BY4BHH:MI:SSDS(F)') as varchar(40)) as myTimeZone2

2011-04-08 21:16:10.110000+00:00

2011-04-08 21:16:10.110000


This gets the date, how can I get the full timestamp?

SELECT (CAST('1970/01/01' AS DATE FORMAT 'yyyy/mm/dd')) + CAST(my_time/86400 AS INTEGER) AS CONVERTED_TIME
FROM myDatabase.myTable

I have a table with a column that is an integer and has Unix epoch time data.


Is there a way to convert TIMESTAMP data to ISO 8061 format, so the result will be something like: ' 2011-04-04T12:57:18.530'?

My data:
15-MAR-11 AM
15-MAR-11 AM
15-MAR-11 AM
15-MAR-11 AM
15-MAR-11 AM

My current DDL:
CREATE multiset TABLE dbname.dbtable
CREATE_DATE timestamp(6)

My current TPT Insert statement:
'INSERT INTO '||@TargetDatabase||'.dbtable

The data is being stored in the database table with century of '19' instead of '20'. My data will always be century of '20':

1911-03-15 07:54:08

Hello Folks,
I am new to Teradata.

I have a date column in one table (Table A) that I have to insert into a date column in another table (Table B)

Table A date type: TIMESTAMP (6)
Table B date type: TIMESTAMP (0)

Problem: How do you add or subtract a "fractional second" to a TIMESTAMP column in Teradata?

The available documentation is not very clear, so let's look into it using examples.

Basic use of INTERVAL

Build, Populate, and Query Test Table

I have a strange error.
I am creating one table with foreign key on the same table :



I insert some data with java program :

PreparedStatement pstmt =null;
pstmt= conn.prepareStatement("INSERT INTO TEST_B (TID,BACTIVE,PASSRESETDATE,TEST_ID) VALUES (?,?,?,?)");
for(int i=1;i<3;i++){


Would anyone know why the difference of two timestamp(6) columns in interval day(4) to second(6) is coming out all nulls in Hyperion 9.3.1? A calculated field with formula "(col2 - col1) interval day(4) to second(6)" is always NULL even though the two columns have different timestamp values.

Just to give you an idea, below is the equivalent in SQL Assistant that works:

create volatile table mytable ( col1 timestamp(6) , col2 timestamp(6) ) primary index (col1) on commit preserve rows;

delete from mytable;
insert into mytable
values (

From the TD user manual, i got this as an example of timestamp to time conversion:

SELECT CAST(cast('1997-12-31 23:59:59' as timestamp(5)) AS TIME(5) WITH TIME ZONE);

But why the time zone? how can I extract just the time with no timezone. I didn't define any timezones anywhere...

Any thoughts on this would be great