Teradata equivalent function to FROM_TZ in Oracle

Database
Enthusiast

Teradata equivalent function to FROM_TZ in Oracle

Hi,

Is there any built in function available in Teradata with similar functionality as FROM_TZ  function in Oracle?

Or we will have to do it using INTERVAL or CAST function?

Khurram
Tags (2)
6 REPLIES
Enthusiast

Re: Teradata equivalent function to FROM_TZ in Oracle

Hi Khurram,
This is we normally do:
First we run the help session to see the current setting;
help session;

You can set the default time zone at DBS control or User level when you create user (TIME ZONE = LOCAL,sign 'time_zone_string','quotestring',NULL) or
SET TIME ZONE LOCAL ,SET TIME ZONE USER ,SET TIME ZONE INTERVAL ‘00:00’ HOUR TO MINUTE

So when you create table with data type TIMESTAMP(0) WITH TIME ZONE and insert values like (TIMESTAMP '2013-12-03 10:30:00'), you can see the result of it.

Now when you want with TIMESTAMP(0) then you can cast with ----CAST(timestmp_with_zone AS TIMESTAMP(0) when you select.

You can try and do other settings too.

example:
ct rkt_test
(geo_id CHAR(3),
timestmp_with_zone TIMESTAMP(0) WITH TIME ZONE);

Cheers,
Raja
Senior Supporter

Re: Teradata equivalent function to FROM_TZ in Oracle

wouldn't it be just a cast?

select cast('2012-12-12 23:12:59+03:00' as timestamp(0) with time zone);

If you need FROM_TZ with two inpurt parameter you could create a SQL udf

where you !! the two input parameter...

Enthusiast

Re: Teradata equivalent function to FROM_TZ in Oracle

Thanks Raja and Ulrich,

I think I will have to go for Ulrich's approach to write a UDF.

Khurram
Enthusiast

Re: Teradata equivalent function to FROM_TZ in Oracle

Ulrich,

Can we use Oracle UDF to do this? Just like we use oReplace?

just a thaught, please guide!

Khurram
Senior Supporter

Re: Teradata equivalent function to FROM_TZ in Oracle

REPLACE FUNCTION mydb.FROM_TZ (ts_str char(19),tz_str char(6) )
RETURNS timestamp(0) with time zone
SPECIFIC mydb.FROM_TZ
RETURNS NULL ON NULL INPUT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER INLINE TYPE 1
RETURN
CAST((ts_str||tz_str) as timestamp(0) with time zone);

select mydb.FROM_TZ(casT('2012-12-12 23:26:20' as char(19)),cast('-03:00' as char(6)))

 

Enthusiast

Re: Teradata equivalent function to FROM_TZ in Oracle

Thank you Ulrich :)

Khurram