Teradata DateTime Syntax

General

Teradata DateTime Syntax

Greetings,

I am new to TD world.  I am using Teradata SQL Assistant tool to insert a row into one table. Here is the query:

INSERT INTO TABLES.EvtTest (EvtID, LocalDT, GMTDT, InsertDT,eventMsg) Values (1099,'10/18/2016 11:28:07 AM','10/18/2016 6:28:07 PM','10/18/2016 11:28:19 AM','....'); 

However, I got the error referring to the invalid DateTime syntax for 3 fields.   

 

These three datetime values I got from .NET.   What is the correct syntax in TD world?  

 

Thanks

John

 

When I run:

select current_timestamp

I get:  10/18/2016 14:43:29.080000-07:00

 

td.jpg

 

 

 


Accepted Solutions

Re: Teradata DateTime Syntax

I managed to play around with it and solved the issue by using this syntax:

"CAST('" & str & "' AS TIMESTAMP FORMAT 'MM/DD/YYYYbHH:MI:SS')"

Where str is in format as:   

Dim StringFormat As String = "MM/dd/yyyy HH:mm:ss.ffffff"

Problem solved.   Writing it down in case someone else has the same issue.   

 

TD syntax is weird! :P 

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Teradata DateTime Syntax

You should use the ISO/ANSI standard format for hardcoded date/time literals.

In SQL Assistant change Options -> Data Format -> Display dates in this format to 2009-12-25.

 

And then it's 

 

 

DATE '2016-10-18'
TIMESTAMP '2016-10-18 11:28:07'
TIMESTAMP '2016-10-18 18:28:07'
TIMESTAMP '2016-10-18 18:28:07-07:00'
TIME '18:28:07'

Re: Teradata DateTime Syntax

I managed to play around with it and solved the issue by using this syntax:

"CAST('" & str & "' AS TIMESTAMP FORMAT 'MM/DD/YYYYbHH:MI:SS')"

Where str is in format as:   

Dim StringFormat As String = "MM/dd/yyyy HH:mm:ss.ffffff"

Problem solved.   Writing it down in case someone else has the same issue.   

 

TD syntax is weird! :P