Hi, I am trying to create my first function in Teradata SQL Assistant using a script I found on the internet.  the purpose of the function is to convert a timestamp to a unix timestamp and is as follows;
 
CREATE OR REPLACE FUNCTION oracle_to_unix(in_date IN DATE) RETURN NUMBER 
IS 
BEGIN 

Hi All,
I am trying to replicate the below vertica conversion to timestamp to teradata, But I am getting error. Can someone please guide me?
Vertica:
to_timestamp('June 7, 2016, Hour 0','Month DD, YYYY, Hour HH') 

--output:

'2016-06-07 00:00:00'

Hi All,
 
I have data like these . 

Mar 22, 2016 12:06:35 PM

I am using mload to load in a large data set. However, I can't get any of hte data to load because of the error 6760. I know it deals with the 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)?
e.g.
1439374041   ->   2015-08-12 10:07:21.000000

Hello,
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!
 

Hello, 
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'?
SELECT DISTINCT

TimeStamp(FORMAT 'YYYYMMDD') (CHAR(8))  AS CALL_DT,

TimeStamp(FORMAT 'HH:MI:SS') (CHAR(8))  AS CALL_TIME,

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.

Hi,
 
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

Hi,
 
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.
 
CT STGDB_DV1.ST_TIMESTAMP_TEST
(
EMP_ID INTEGER,

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) 
Data:
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 ?   

Hi,
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 ?
 
Regards,

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_

Hi,
  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.
DATE_COL - DATE FORMAT 'YY/MM/DD'
TIME_COL - TIME(6)
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'

Hello.
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.

Hello,

Hi,
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 :
YYYY-MM-DD HH:MI:SS NNNNNN 

Hello,

Hi
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
 
Thanks
Balu
 

Hi,
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')
o/p
12/12/1912 00:00:00
tried with the current_timestamp(0)
eg:
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

Hi
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')
o/p
12/12/1912 00:00:00
tried with the current_timestamp(0)
eg:
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
o/p

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 
device_dt_ut TIMESTAMP(0) WITH TIME ZONE
​snippet of fastload:
 

0005 DEFINE

        device_dt_ut (VARCHAR(32)),

Hi all,

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?

Hello,

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?

Regards,

ASR

Hi,

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.

Hello,

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?
Thanks!

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

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:

SQL:
SELECT
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

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

myTimeZone
2011-04-08 21:16:10.110000

myTimeZone2

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.

Hi

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 07.54.08.000000 AM
15-MAR-11 07.53.40.000000 AM
15-MAR-11 07.53.04.000000 AM
15-MAR-11 07.52.55.000000 AM
15-MAR-11 07.52.16.000000 AM

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

My current TPT Insert statement:
'INSERT INTO '||@TargetDatabase||'.dbtable
(
CREATE_DATE = :CREATE_DATE (TIMESTAMP, FORMAT ''DD-MMM-YYBHH.MI.SS.S(6)BT'')
);'

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

CREATE_DATE
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)

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