Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 29 Sep 2006 @ 17:23:03 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Operation on timestamps Teradata vs Oracle
 
From:   Dieter Noeth

Andrea Cappelli wrote:

  Does anyone know if exist a function like the ORACLE Trunc.  


Should the result be a DATE or a TIMESTAMP?

     SELECT
        CAST(CURRENT_TIMESTAMP(0) AS DATE),
        CAST(CAST(CURRENT_TIMESTAMP(0) AS DATE) AS TIMESTAMP(0));

       *** Query completed. One row found. 2 columns returned.
       *** Total elapsed time was 1 second.

     Current TimeStamp(0)  Current TimeStamp(0)
     --------------------  --------------------
                2006-09-29   2006-09-29 00:00:00

If it's TRUNC(datecol, format) it's more complicated, e.g.:

     SELECT
        (EXTRACT(YEAR FROM CURRENT_TIMESTAMP(0)) -1900) * 10000 + 101 (DATE)
     (TIMESTAMP(0)) AS "TRUNC(datecol, 'YEAR')";

       *** Query completed. One row found. One column returned.
       *** Total elapsed time was 1 second.

     TRUNC(datecol, 'YEAR')
     ----------------------
         2006-01-01 00:00:00

  I don' t believe that in teradata is it possible with a function,  


Should be easy to write a UDF for that...


  and also format the time in this way seems very hard.  


That format is not following any standard, do you really need that format, without the period it's easier to read:

     2006-01-01 10:56:00

  Another question: Is it possible use the add_month() on a timestamp field? I don' believe.  


Why don't you try it?

     SELECT ADD_MONTHS(CURRENT_TIMESTAMP, 1);

       *** Query completed. One row found. One column returned.
       *** Total elapsed time was 1 second.

     ADD_MONTHS(Current TimeStamp(6), 1)
     -----------------------------------
         2006-10-29 17:17:06.710000+02:00

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023