All Forums Database
10 Aug 2012
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)

Thanks & Regards, Venkatesh Kumar Saluvathi
veehell 19 posts Joined 07/12
10 Aug 2012

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-

veehell 19 posts Joined 07/12
10 Aug 2012

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-

You must sign in to leave a comment.