Converting TSQL DATEADD(MINUTE to Teradata SQL

Database
Enthusiast

Converting TSQL DATEADD(MINUTE to Teradata SQL

How do you convert the following from TSQL to Teradata SQL?  

CAST(CAST(CAST(t.NOM_DATE AS DATE) AS DATETIME) + CAST(CAST(DATEADD(MINUTE, t.START_MOMENT - tzd.BIAS_MI, '1899-12-30') AS TIME(7)) AS DATETIME) AS DATETIME) AS 'START_TIME'

Thanks in advance!

SP

 

3 REPLIES
Highlighted
Junior Contributor

Re: Converting TSQL DATEADD(MINUTE to Teradata SQL

What are the actual data types of those columns?

Can you show some data and expected result?

Enthusiast

Re: Converting TSQL DATEADD(MINUTE to Teradata SQL

Hi.  Thanks for the quick response.

 

t.NOM_DATE TIMESTAMP(3) = 2017-11-02 00:00:00.000

CAST(t.START_MOMENT AS INT) = 61980270  originally DECIMAL(15,1) 

CAST(tzd.BIAS_MI AS INT) = 360    originally DECIMAL(15,1) 

 

Expected (SQL Server) Results: 2017-11-02 14:30:00.000

Teradata Employee

Re: Converting TSQL DATEADD(MINUTE to Teradata SQL

Seems odd to pick the date portion from one timestamp and the time portion from what is effectively another timestamp.

Teradata INTERVAL data types are limited in precision, so if you wanted to convert the adjusted "START_MOMENT" to a timestamp you'd need to do the date and time portions separately. But since the calculation is ignoring the date portion, this should give you the results you expect:

 

CAST(CAST(t.NOM_DATE AS DATE) AS TIMESTAMP(3)) + (CAST(t.START_MOMENT - tzd.BIAS_MI AS INTEGER) MOD 1440) * INTERVAL '1' MINUTE AS 'START_TIME'