What is the Equivalent Function of TRUNC for Date in Teradata??

Database

What is the Equivalent Function of TRUNC for Date in Teradata??

Hi All,

I have some idea of TRUNC for Date and Number in Oracle,but not in Teradata..How to use it in teradata...Can anyone explain....

     TRUNC(Column_Name)

Tags (1)
2 REPLIES
Teradata Employee

Re: What is the Equivalent Function of TRUNC for Date in Teradata??

There are three different dataypes in Teradata/Standard SQL: 

- DATE, just the date

- TIME, just the time, optionally WITH TIME ZONE

- TIMESTAMP, date plus time, optionally WITH TIME ZONE

Oracle's DATE and TIMESTAMP both map to Teradata's TIMESTAMP. So you better think about the right datatype before truncating all the time.(copy paste from 'Dnoeth' post)

TD stores datatype DATE by this formula:

(YEAR - 1900) * 10000 + (MONTH * 100) + DAY  

so you can actually retrieve it in 'numeric' format and make some 'calculation' (using macro or UDF) to simulate Oracle TRUNC as you know it.

There are also CAST and EXTRACT and FORMAT to get different output with different options ...

Reffer to Teradata SQL Reference vol.3 DataTypes and Literals. Here are some links, it might help you more.... * http://www.teradataforum.com/l081007a.htm * http://forums.teradata.com/forum/database/oracle-to-teradata * http://forums.teradata.com/forum/database/few-basic-queries *

It really depends on what you want to achieve ;-)

Cheers
-=[VH]=-
Teradata Employee

Re: What is the Equivalent Function of TRUNC for Date in Teradata??

In TD there is no TRUNC, but you can download UDF functions, to get 'popular' Oracle's, including 'TRUNC' ....

http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions

TBH, operation(s) with date/datetime datatypes are much easier in TD, especially when you use internal 'calendar'.

Cheers
-=[VH]=-