All Forums UDA
aolympio 25 posts Joined 04/09
13 Apr 2009
CONVERT and DATEDIFF in Teradata

Hi!I want to know how can I use these commands (from SQL Server) in Teradata because i need to obtain the similar functionality: and .For example:SELECT FIELD FROM TABLE1 WHERE (DATEDIFF(ss,(SELECT FIELD1 FROM TABLE1), SOME_DATE));SELECT CONVERT(VARCHAR(23),ACM_DATE,121) FROM TABLE1;Thanks for help,Anderson

Adeel Chaudhry 773 posts Joined 04/08
14 Apr 2009

Hello,It is a good idea, not to flood the forum with same question! :)DATEDIFF can be converted to SQL or UDF from OracleUDFs or custom UDF (perhaps the best option :)).CONVERT can be converted to a combination of CAST and FORMAT.HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

GopiKrishnan S 18 posts Joined 02/08
20 Apr 2009

Hi,I guess, DATEDIFF functionality can be performed using INTERVAL functions as below,Field1 - Field2 SECOND(N)Field1, Field2 - columns defined with datatype as TIMESTAMP or TIME,N - 1 to 4, number of digits needed for the output, n=3 will give you difference upto 999 seconds, difference above that will throw error.Similarly we can use MINUTE(N) and HOUR(N) for time difference.If the columns are defined with datatype as DATE or TIMESTAMP, then we can use YEAR(N), MONTH(N) and DAY(N)

RevathiS 5 posts Joined 08/16
18 Aug 2016

Hi, For this SQL Query
select DATEDIFF(DAY,'2016-08-20', convert (SMALLDATETIME, {fn CURDATE()}))..
I need to achieve this in Tera data. Since datediff is problem to work with here in tera data..
Kindly help me with this...

dnoeth 4628 posts Joined 11/04
18 Aug 2016

For difference in days it should be SELECT CURRENT_DATE - DATE '2016-08-20'


RevathiS 5 posts Joined 08/16
18 Aug 2016

Hii Dieter,
Thanks. How can i handle this convert (SMALLDATETIME, {fn CURDATE()}--> epoch time format in teradata.??

dnoeth 4628 posts Joined 11/04
19 Aug 2016

See the TimeStamp_to_UnixTime UDF found in


You must sign in to leave a comment.