Issue with UTCTOLOCAL Function in Data Flows (SAP Datasphere)

[gangareddysomu]

Hi SAP Community,

I am encountering an issue while working on a calculated column (CD_SNAPSHOT_DATE) in Data Flows in SAP Datasphere.

We have  calculated columns in a Fact View to derive the current date using the following expression:

TO_DATS(UTCTOLOCAL(CURRENT_TIMESTAMP, 'US/Central')).

I need to create a Calculated Column similar to this in Data Flow For Snapshot Date if I use the above expression I am getting the below error:

UTCTOLOCAL function only supports UTC offset timezone formats.

To address this, I updated the expression to use a UTC offset format as follows:

1.TO_DATS(UTCTOLOCAL(CURRENT_TIMESTAMP, '-06:00'))

2.TO_DATS(UTCTOLOCAL(CURRENT_TIMESTAMP, 'UTC-6'))

However, this also throws the same error.

Questions:

1. Does anyone know what UTC offset timezone formats are specifically supported in Data Flows?
2. Are there any working examples or alternatives to dynamically handle time zones in Data Flows, especially for US/Central time?

Thank you in advance for your guidance and suggestions!

Best regards,
Ganga Reddy Somu

[ChintS]

Did you resolve this issue ? I am having the same requirement in Datasphere Dataflow.

[amithrajkr-1]

It worked for me with TO_DATE(UTCTOLOCAL(CURRENT_UTCTIMESTAMP(),'UTC+5.30'))

[YogeshRehadwa]

Hi,

You can try using the below option:

TO_DATE(UTCTOLOCAL (CURRENT_UTCTIMESTAMP, 'EST') )

https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/20f52f4475191014a10892424...

[Githen_Ronney1]

Hi,

You can create a new calculated column of data type 'Date' and use the expression CURRENT_UTCDATE().

Regards

Githen

[gangareddysomu]

Hi Githen, Thanks for your response! Yeah, I tried using CURRENT_UTCDATE(), and it is working. However, my requirement is to adjust the date dynamically to US/Central time, ensuring it accounts for Daylight Saving Time changes accordingly. If you have any suggestions for handling this scenario, I’d greatly appreciate it! Best regards, Ganga

[Githen_Ronney1]

Hi, 

You will be able to calculate dynamically using Python script operator. You may have to adjust below script based on your dataframe structure.

# Helper function to calculate the second Sunday of March

def get_second_sunday(year):

    # March 1st

    first_day = pd.Timestamp(year=year, month=3, day=1)

    # Find the first Sunday (add the days until Sunday)

    first_sunday = first_day + pd.Timedelta(days=(6 - first_day.weekday()) % 7)

    # The second Sunday is 7 days after the first Sunday

    second_sunday = first_sunday + pd.Timedelta(days=7)

    return second_sunday

# Helper function to calculate the first Sunday of November

def get_first_sunday(year):

    # November 1st

    first_day = pd.Timestamp(year=year, month=11, day=1)

    # Find the first Sunday (add the days until Sunday)

    first_sunday = first_day + pd.Timedelta(days=(6 - first_day.weekday()) % 7)

    return first_sunday

# Function to adjust the timestamp based on DST for US Central Time

def adjust_for_dst(current_date):

    year = current_date.year

    # Get DST start and end dates

    dst_start = get_second_sunday(year)

    dst_end = get_first_sunday(year + 1)  # DST ends in the next year

    # Check if current date is within the DST range

    if dst_start <= current_date < dst_end:

        # Apply UTC-5 for CDT

        adjusted_time = current_date - pd.Timedelta(hours=5)

    else:

        # Apply UTC-6 for CST

        adjusted_time = current_date - pd.Timedelta(hours=6)

    return adjusted_time

# Example usage:

current_date = pd.Timestamp.now(tz='UTC')  # Current UTC time

data[adjusted_date] = adjust_for_dst(current_date)