Date time Zone convention in to AMER , APJ, EMEA regions

Database
Enthusiast

Date time Zone convention in to AMER , APJ, EMEA regions

Hi Team,

 

I have a requirement to convert the Date Time in to different time Zones.

 

I have Ship_Dt in CST time zone but when i process the data for all 3 regions and i need to convert it in to 3 regions based on Region column

 

1. CST time zone to AMER time zone

2. CST time zone to APJ time zone

3. CST time zone to EMEA time zone

 

Need a formulla to convert this time zone which need to take care Day lite savings for those regions as well.

 

Thanks

spk

  • date calculation
  • time zone
8 REPLIES
Senior Apprentice

Re: Date time Zone convention in to AMER , APJ, EMEA regions

How is Ship_Dt defined, does it include a TIME ZONE?

What is your local/session time zone?

 

And what is an EMEA, etc. time zone? These are regions with multiple time zones.

Enthusiast

Re: Date time Zone convention in to AMER , APJ, EMEA regions

It's Just a Date time stamp with out Time Zone.

 

EMEA is Europian time zone. and Local time zone is CST and the and data in Ship_dt also in CST timezone.

Senior Apprentice

Re: Date time Zone convention in to AMER , APJ, EMEA regions

There's no "european time zone", it covers multiple zones (same for APJ/AMER).

 

Try ship_dt AT 'Europe Western'

 

see AT LOCAL and AT TIME ZONE Time Zone Specifiers

Enthusiast

Re: Date time Zone convention in to AMER , APJ, EMEA regions

Can you help me witht the Query to convert in to specific timezone based on Region column, I tryed with the ways but its showing syntax error every time.

 

Senior Apprentice

Re: Date time Zone convention in to AMER , APJ, EMEA regions

What's the failing query?

What is stored in your region column?

Can you add some example rows?

Enthusiast

Re: Date time Zone convention in to AMER , APJ, EMEA regions

this is the error i am getting when i try 

 

ERROR [42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like ')' between an integer and ':'.
SELECT Command Failed.

 

SELECT
SHIP_BY_DATE,
LAST_UPDATED,
CAST(LAST_UPDATED AS INTERVAL HOUR(11:30) TO MINUTE)

FROM WW_CUSTWATCH.ORDER_STATUS_REPORTING_WS

 

Attached is the data that apper in table, Please help me in correcting the syntax.Result.PNG

Senior Apprentice

Re: Date time Zone convention in to AMER , APJ, EMEA regions

Interval syntax goes like this: INTERVAL '11:30' HOUR TO MINUTE

 

And there's no cast: LAST_UPDATED AT '11:30'

or if  minute is zero: LAST_UPDATED AT 11

 

Btw time zone 11:30 doesn't exist :)

Enthusiast

Re: Date time Zone convention in to AMER , APJ, EMEA regions

Hi,

 

it is working for APJ region 

 

select
SHIP_NOTFCN_DT + 8/24 as APJ_Reg_Shtl_dt from SLS_PKG_INC.MDTF_SUPPLY_AMER_INC1

 but it is not workign for AMER convertiosn 

select
CAST(SHIP_NOTFCN_DT AS DATE FORMAT'MM/DD/YYYY') AT time Zone -5 from SLS_PKG_INC.MDTF_SUPPLY_AMER_INC1

 error:

ERROR [HY000] [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.
SELECT Command Failed.

 

select
SHIP_NOTFCN_DT AT time Zone -5 from SLS_PKG_INC.MDTF_SUPPLY_AMER_INC1

 

both giving same error


dnoeth wrote:

Interval syntax goes like this: INTERVAL '11:30' HOUR TO MINUTE

 

And there's no cast: LAST_UPDATED AT '11:30'

or if  minute is zero: LAST_UPDATED AT 11

 

Btw time zone 11:30 doesn't exist :)