3706: Syntax error: Data Type does not match a Defined Type name.

Analytics
Fan

3706: Syntax error: Data Type does not match a Defined Type name.

Hi, I started using Teradata a couple weeks ago and keep getting syntax errors when using certain date functions.

 

I'm trying the run the following sample query with the field event_time as a timestamp in the format 'YYYY-MM-DD HH:MM:SS' (e.g 2017-07-06 14:08:10.667):

 

select top 10
event_time
,date_add(day, 1, event_time) as one_day
from table_A

 

[Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the 'day' keyword.

 

I tried putting quotes around 'day' it and gave me the same error: [Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the string 'day'.

 

I have a similar problem when using the date functionfrom_unixtime(unixtime)

 

I run the following query where the field tracking_time is a bigint in milliseconds (e.g. 1499378648326):

 

select top 10
from_unixtime(tracking_time/1000)
from table_B

 

[Error 3706] [SQLState 42000] Syntax error: Data Type "tracking_time" does not match a Defined Type name.

 

I tried manually adding the unix time parameter like below and got another error:

 

select top 10
from_unixtime(1499378649)
from table_B

 

[Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the integer '1499378649'.

 

I'm not very familiar with the syntax yet. Could anyone explain the proper way to use these functions? Thanks!


Accepted Solutions
Junior Contributor

Re: 3706: Syntax error: Data Type does not match a Defined Type name.

Both Date_Add and From_Unixtime are no Standard SQL, but proprietary functions (you probably worked with MySQL before), don't expect different DBMSes to support syntax like this as-is.

 

date_add(day, 1, event_time) 
-- translated to Standard SQL event_time + interval '1' day

For From_Unixtime see the UDFs in following thread:

 

convert 13 digit epoch time to date?

 

 

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: 3706: Syntax error: Data Type does not match a Defined Type name.

Both Date_Add and From_Unixtime are no Standard SQL, but proprietary functions (you probably worked with MySQL before), don't expect different DBMSes to support syntax like this as-is.

 

date_add(day, 1, event_time) 
-- translated to Standard SQL event_time + interval '1' day

For From_Unixtime see the UDFs in following thread:

 

convert 13 digit epoch time to date?

 

 

 

Fan

Re: 3706: Syntax error: Data Type does not match a Defined Type name.

Thank you! Both those solutions worked for me including the to_timestamp function detailed in your other link.