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....
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 ;-)
In TD there is no TRUNC, but you can download UDF functions, to get 'popular' Oracle's, including 'TRUNC' ....
TBH, operation(s) with date/datetime datatypes are much easier in TD, especially when you use internal 'calendar'.