Archives of the TeradataForum

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

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

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>>