How to Convert Date field to PST timezone where the date field can be in any timezone

Analytics
Enthusiast

How to Convert Date field to PST timezone where the date field can be in any timezone

Hello All,

 

I have a date column whose timezone can be identified in another column(Transaction_Date_TZ) and i want to convert the date to PST timezone.

 

None of the date time functions are working like ENT.LOG_TMSTMP.  Could you please help me with this conversion.

 

We are on Terdata 16 version.

 

Transaction_DateTransaction_Date_TZTransaction_Date_PST
2016-07-12 12:40:56.000UTC 
2016-06-11 15:40:56.000BRT 
2016-07-01 10:40:56.000MDT 

 

Thanks,

Chava

3 REPLIES 3
Ambassador

Re: How to Convert Date field to PST timezone where the date field can be in any timezone

You need to translate your time zone abbreviation string to one of the supported time zone strings using a CASE,e.g.:

,Transaction_Date AT 
   CASE Transaction_Date_TZ
      WHEN 'UTC' THEN 'GMT'
      WHEN 'BRT' THEN 'America Brazil'
      WHEN 'MDT' THEN 'America Mountain' 
etc. END;

Of course this CASE is a perfect candidate for a SQL UDF -)

 

Enthusiast

Re: How to Convert Date field to PST timezone where the date field can be in any timezone

Thank you for the response.

 

Is there any dynamic way other than Case statement. timezone values are not limited to the mentioned above, it can be one of any valid timezone like AET,EET,IST etc..

Ambassador

Re: How to Convert Date field to PST timezone where the date field can be in any timezone

But the possible list of time zones is limited and fixed, thus easily implemented as CASE.