TIMESTAMP in TeraDATA

UDA
Enthusiast

TIMESTAMP in TeraDATA

Hi

I am new to the Topics in Teradata and having the Column Table is

UPDATE_DTS TIMESTAMP(0) NOT NULL

and the data in the Columns is '2009-01-16 02:29:08'

i want to select the data based on the Column for the Particular Month. any help will be appreciated

SELECT *
FROM Table
where Update_dts >= '2001-01-01' and Update_dts <= '2001-01-31';

i have tried with different scenarios but having the issues ...

SELECT *
FROM Table
where Update_dts = '2001-01-01' ;

SELECT *
FROM Table
where Update_dts = '2001-01-01 4:05:30 PM'

Error: 6760 Invalid TimeStamp

Thanks in Advance
6 REPLIES
Enthusiast

Re: TIMESTAMP in TeraDATA

The following code demonstrates answers to your problems:

Create Volatile Table T1( PK Smallint Not Null, Update_DTS TimeStamp(0) Not Null)
Unique Primary Index (PK)
On Commit Preserve Rows;
Insert Into T1 Values (1, '2009-01-16 02:29:08') ;
Insert Into T1 Values (2, '2009-01-16 16:05:30') ;

Select * From T1 Where Extract (Year From Update_DTS) = 2009
And Extract (Month From Update_DTS) = 1;

Select * From T1 Where Cast(Update_DTS As Date) = '2009-01-16' ;

Select * From T1 Where Cast(Update_DTS As Date) = Date '2009-01-16' ;

Select * From T1 Where Update_DTS
= '2009-01-16 04:05:30 PM' (Timestamp(0), Format 'yyyy-mm-ddbhh:mi:ssbt') ;

-- Note the use of "b" to denote a space in the above. For this format, you must use b, not space.
(SQL Reference, Data Types And Literals Manual)
Enthusiast

Re: TIMESTAMP in TeraDATA

Alternatively,
you could also change your where clause to read

where update_dts between '2009-01-16 00:00:00' and '2009-01-31 23:59:59'

this change may also improve the performance of your query. Using any functions in a where clause tends to cause full table scans...
Enthusiast

Re: TIMESTAMP in TeraDATA

@ Velvet Elvis,@ Jimm

Thanks for the Help and it Works fine ....

Can u share the Link for the Doc's

Re: TIMESTAMP in TeraDATA

Hi,

when trying to run the below query iam getting Invalid Time error.

sel (CASE

WHEN (LENGTH(b.vndr_no)=6 or LENGTH(b.vndr_no) is null)

THEN CAST(SUBSTR(B.Vndr_no,1,2)|| ':' || SUBSTR(B.Vendor_no,3,2)|| ':' || SUBSTR(B.Vndr_no,5, 2) AS TIME)

ELSE cast(b.vndr_no as time)

END) AS TRAN_END_TIME from TableA

The problem is Vndr_no colunm is a Varchar(1500) and needs to converted to time.

In vndr_no column we have few rows with length=6 and few as null for both these iam able to insert value to a table but there are few rows in this column which are timestamp or length<>6 so while converting these to time iam getting Invalid time error.

Can anyone please help me to solve this issue.Need to convert all rows to time datatype.

Thanks in advance.

Regards,

Josh

Senior Apprentice

Re: TIMESTAMP in TeraDATA

Hi Josh,

What's the actual data in this column, always a time or a timestamp or something else, too?

Why is that column defined as Varchar(1500) then?

Re: TIMESTAMP in TeraDATA

Hi Dieter,

While  importing the  data from csv file to table through SQLA.

Im getting below error 6760 invalid timestamp.