Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 28 Feb 2008 @ 12:55:39 GMT


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


Subj:   Re: Info regarding teradata function
 
From:   jonas.blomqvist

Gagan,

There are some UDF's available from teradata for a limited set of Oracle-SQL functions, see:

www.teradata.com/DownloadCenter


...creating additional UDF's can be done for most Oracle-SQL functions, at least with limited (but usually sufficient) functionallity.

If you can't use UDF approach, you have to make do with constructs using standard Teradata-SQL, below are examples on how to do the equivalents you asked for.


Jonas Blomqvist


Oracle-SQL:

     TO_CHAR(TO_DATE(value, 'DDMMYYYY'), 'D')

Equivalent Teradata-SQL construct:

     1+(POSITION(''||CAST(CAST(value AS DATE FORMAT 'DDMMYYYY') AS FORMAT 'E3')
           IN 'SunMonTueWedThuFriSat')/3)

Oracle-SQL: NEXT_DAY(d /*datetime_value*/, c /*char_day_name*/) Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language. The return value has the same hours, minutes, and seconds component as the argument d.

Equivalent Teradata-SQL construct (for d of TimeStamp(6) datatype):

     CAST((CAST(
       (CAST(d AS DATE)
       +(CASE WHEN ((POSITION(SUBSTRING(c FROM 1 FOR 3)
           IN 'SunMonTueWedThuFriSat')
         - POSITION(''||CAST(CAST(d AS DATE) AS FORMAT 'E3')
           IN 'SunMonTueWedThuFriSat')
         ) / 3) < 1
        THEN 7 + (POSITION(SUBSTRING(c FROM 1 FOR 3)
           IN 'SunMonTueWedThuFriSat')
         - POSITION(''||CAST(CAST(d AS DATE) AS FORMAT 'E3')
           IN 'SunMonTueWedThuFriSat')
         ) / 3
        ELSE (POSITION(SUBSTRING(c FROM 1 FOR 3)
           IN 'SunMonTueWedThuFriSat')
         - POSITION(''||CAST(CAST(d AS DATE) AS FORMAT 'E3')
           IN 'SunMonTueWedThuFriSat')
         ) / 3
        END)
       ) AS FORMAT 'YYYY-MM-DD') || ' '
       || CAST(CAST(CAST(d AS TIME(6)) AS FORMAT 'HH:MI:SS.S(6)') AS CHAR(15))
     ) AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)')


     
  <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