Sybase to Teradata

Database
Enthusiast

Sybase to Teradata

This following query works on Sybase:

Need help on making this work on Terdata.

execute (

     select   max (convert(int,floor(feed_key * 1.00 / 100.0))) as feed_max,

            convert(date,dateadd(day, cast(right(left(convert(varchar(50),

           max(convert(int,floor(feed_key * 1.00 / 100.0)))               

           ),7),3) as int) - 1,

           convert(datetime, left(convert(varchar(50),

           max(convert(int,floor(feed_key * 1.00 / 100.0)))

           ),4) + '0101', 112))) as last_update_date,

        max(acct_link_dt) as mxCust_acct_link_dt,

        Year(max(acct_link_dt)) as mxCust_acct_link_year,

        Month(max(acct_link_dt)) as mxCust_acct_link_month

     into  #dates_indiv      

     from Indiv

    ) by sybase;

5 REPLIES
Senior Apprentice

Re: Sybase to Teradata

You need to rewrite the proprietary Sybase syntax to Standard SQL:

CONVERT(datatype, column) -> CAST(column AS datatype) 
YEAR/MONTH(column) -> EXTRACT(YEAR/MONTH FROM column)

FLOOR is built-in in TD14, what's your release?

The DATEADD might be quite simple, but I don't know what you're actually calculating, could you elaborate and show some example data?

Enthusiast

Re: Sybase to Teradata

feed_key is a julian date :201425300

need to derive the other variables which use feed_key.

feed_max which is: 2014253

last_clic_update_date:2014/09/10

Senior Apprentice

Re: Sybase to Teradata

What's the datatype of feed_key?

Enthusiast

Re: Sybase to Teradata

numeric

Senior Apprentice

Re: Sybase to Teradata

Try

max(cast(cast(feed_key /100 as char(7)) as date format 'yyyyddd')) as last_update_date

And you don't need FLOOR as a CAST to INT is the same as FLOOR:

cast(feed_key / 100 as int) as feed_max

extract(Year from max(acct_link_dt)) as mxCust_acct_link_year