All Forums Database
M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013
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

Raja_KT 1246 posts Joined 07/09
03 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
03 Dec 2013

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013

Thanks Raja and Ulrich,
I think I will have to go for Ulrich's approach to write a UDF.
 

Khurram

M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013

Ulrich,

 

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

 

just a thaught, please guide!

 

 

Khurram

ulrich 816 posts Joined 09/09
03 Dec 2013
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)))

 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013

Thank you Ulrich :)
 

Khurram

You must sign in to leave a comment.