SQL: Informix to Teradata 1

[witchblade]

I am converting data models from Informix to Teradata, and am having a difficult time with some of the SQL.

Right now I have a calculated time field in Informix that subtracts the end time from the start time, giving me a closed duration.

NOTE: Timestamp datatype in Informix is "datetime year to second", in Teradata the datatype is "timestamp".
The syntax in Informix is as follows:
(table_name.end_tms - table_name.start_dte) - (extend(table_name.start_tim , year to second) - today)

Can anyone help me convert this properly to Teradata?

 

[craig322]

I had a similar thing I did to find total time. I stored
the beginning and ending times, which are stored as integers (even though you specify a time datatype). I used cast and substring to disect the time, then performed
the calculations manually with each interval (hours, minutes, and seconds). Once I arrived at my end times, I reassembled the value into a total time. Feel free to e-mail me if this sounds like it will work for you.

Craig

 

[tdatgod]

Hi,
SQL isn't my strong suit, but can't you just simply subtract the 2 timestamps and get an interval?

On the other hand, Looking at the above SQL

(table_name.end_tms - table_name.start_dte) - (extend(table_name.start_tim , year to second) - today)

teradata supports Neither the function 'Extend' nor the function 'Today'.

however it appears Extend is the ANSI INTERVAL specification or maybe just a CAST operation.

(Interval table_name.start_tim year to second)

However we have a teradata specific function called CAST if you don't care about portability.

Cast ( table_name.start_tim as timeStamp)


'Today' should be specified in teradata syntax as

Current_date ( 01/08/13 )
Current_time ( 13:00:00 )
Current_timestamp ( 2001/08/13 13:00:00 )

Depending on which Type you want the result to be.





if you want to look at the Teradata documentation you can go to.... ( from a previous post )


--------------------------------------------------------

Online documentation for All NCR products including CRM applications, The teradata database, and Cash registers/ATMs.

http://www.info.ncr.com

Specific to the Teradata data Warehousing offering

http://www.info.ncr.com/sd/sd-1.cfm

Specific to the Teradata Database

http://www.info.ncr.com/sd/sd-1.cfm?page=td

Then select SQL REFERENCE.

--------------------------------------------------------

Volume 5 Chapter 5 and 13 in the v2r4.1 documentation is a discussion on Teradata Date/Time expressions.

In the v2r3 documentation that same chapter is Volume 3 chapter 6.

Just make sure you pick the one which corresponds to the Teradat version you are using.

The latest version V2r4.1 was released 2 weeks ago (July 30, 2001).

Let me know if I can be of any further help.

 

[witchblade]

Thank you for your help. I'm still learning SQL, but the links to NCR look very helpful.