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!
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
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'