All Forums UDA
SeenuGuddu 15 posts Joined 07/11
06 Jul 2011
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

Jimm 298 posts Joined 09/07
06 Jul 2011

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)

VandeBergB 182 posts Joined 09/06
06 Jul 2011

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

Some drink from the fountain of knowledge, others just gargle.

SeenuGuddu 15 posts Joined 07/11
07 Jul 2011

@ Velvet Elvis,@ Jimm

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

Can u share the Link for the Doc's

Jyothsna4 1 post Joined 08/15
24 Aug 2015

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

Regards,
Josh

dnoeth 4628 posts Joined 11/04
24 Aug 2015

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?

Dieter

Arunkumargyara 3 posts Joined 03/16
13 Apr 2016

Hi Dieter,
 
While  importing the  data from csv file to table through SQLA.
Im getting below error 6760 invalid timestamp.
 
 

Arun

You must sign in to leave a comment.