CONVERT and DATEDIFF in Teradata

UDA
Enthusiast

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
6 REPLIES
Teradata Employee

Re: CONVERT and DATEDIFF in Teradata

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

Enthusiast

Re: CONVERT and DATEDIFF in Teradata

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)
Enthusiast

Re: CONVERT and DATEDIFF in Teradata

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

Junior Contributor

Re: CONVERT and DATEDIFF in Teradata

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

Enthusiast

Re: CONVERT and DATEDIFF in Teradata

Hii Dieter,

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

Junior Contributor

Re: CONVERT and DATEDIFF in Teradata