SQL query to Teradata SQL

UDA
Enthusiast

SQL query to Teradata SQL

hi,

pls convert the following SQL queries to Teradata SQL.

1.

DATEDIFF(day,dateadd ( dd , ( r_update_date % 1000 ) - 1 , convert ( datetime ,convert( char(4) , r_update_date / 1000 ) + '/01/01') ) , GETDATE()) > 90

2.

datepart(d,dateadd(day,-datepart(d,getdate()),getdate());
5 REPLIES
Junior Contributor

Re: SQL query to Teradata SQL

Hi Rakesh,
what's the datatype of r_update_date, it can't be datetime, looks like an integer/decimal with julian day within year and that dateadd converts it to a date?

CURRENT_DATE - CAST(CAST(r_update_date AS CHAR(7)) AS DATE FORMAT 'yyyyddd')

Dieter
Enthusiast

Re: SQL query to Teradata SQL

r_update_date is a CHAR datatype.

Re: SQL query to Teradata SQL

hi All

i have a similar question. i am trying to convert a sql query(i think it was used in SQL 2000 or Oracle before) to pull data from a Teradata DB. i have already tested the connection and was able to make a simple query return results. however,when i try to run a slightly complex query in Teradata SQL Assistant,i am getting error. here is the query:

SELECT
value1 = t1.column1
from
Table1 t1


the error i am getting is:

this is my 1st time using Teradata,and my sql skills wasnt so good to begin with. hope someone can point out what is the error.

thanks alot
Enthusiast

Re: SQL query to Teradata SQL

Hi Team,

Oracle query :
select row_wid,
X_ATTRIB_27,
X_ORD_DT_CREATED,
abs((TO_NUMBER(TO_CHAR(T61292.X_ATTRIB_27, 'mi'), '99') - TO_NUMBER(TO_CHAR(( CAST(T61292.X_ORD_DT_CREATED as DATE) + ( 330 / 1440 ) ), 'mi'), '99')
+ (TO_NUMBER(TO_CHAR(T61292.X_ATTRIB_27, 'hh24'), '99') - TO_NUMBER(TO_CHAR(( CAST(T61292.X_ORD_DT_CREATED as DATE) + ( 330 / 1440 ) ), 'hh24'), '99') +
(TO_NUMBER(TO_CHAR(T61292.X_ATTRIB_27, 'DDD'), '999') - TO_NUMBER(TO_CHAR(( CAST(T61292.X_ORD_DT_CREATED as DATE) + ( 330 / 1440 ) ), 'DDD'), '999')) * 24) * 60) / nullif( 60, 0)) as c10
from W_ORDER_D T61292
where ROW_WID in ('17858507','17983634','18035254');
Results :-
row_wid X_ATTRIB_27 X_ORD_DT_CREATED C10
17858507 10/1/2008 1:03:29 PM 10/1/2008 7:31:28 AM 0.0333333333333333
17983634 10/7/2008 2:52:37 PM 10/7/2008 9:18:48 AM 0.0666666666666667
18035254 10/11/2008 1:10:50 PM 10/11/2008 7:38:22 AM 0.0333333333333333

 How can we convert above mentioned query into Teradata13.10, Please help from this situation?

Appriciate in advance:)

Enthusiast

Re: SQL query to Teradata SQL

Hi Team,

      Problem is solved :) , by using mentioned logic we achieved

( ((extract(minute from T61292."X_ATTRIB_27")) - (extract(minute from ( T61292."X_ORD_DT_CREATED" + CAST( 330 AS INTERVAL MINUTE(4) ) ))) +

((extract(hour from T61292."X_ATTRIB_27")) - (extract(hour from ( T61292."X_ORD_DT_CREATED" + CAST( 330 AS INTERVAL MINUTE(4) ) ))) +

((extract(day from T61292."X_ATTRIB_27")) - (extract(day from ( T61292."X_ORD_DT_CREATED" + CAST( 330 AS INTERVAL MINUTE(4) ) ))) )*24)*60)) / cast (60 as decimal(34,14)) as c12