TIBCO Scribe Online formulas for SQL Local Time to UTC Conversion

TIBCO Scribe Online formulas for SQL Local Time to UTC Conversion

book

Article ID: KB0078728

calendar_today

Updated On:

Products Versions
TIBCO Cloud Integration - Connect ( Scribe ) Granite

Description

Updating a DateTime field from a SQL source to a Microsoft Dynamics 365 (CRM Online) field may not apply the UTC offset you expect.  Here are 2 sample formulas to test in your environment to determine if they will work for you.

Issue/Introduction

This article provides sample formulas for converting local time to UTC time.

Resolution

 
  1. DATEADD("h",DATEDIFF( "h", GETUTCDATETIME( ), GETDATETIME( ) ),Field_Name_in_SQL)
  2. IF(ISNULL( Field_Name_in_SQL)=TRUE, NULL , DATEADD("h", PARSE(FORMAT("MM/dd/yyyy THH:mm:ss^z", Field_Name_in_SQL), 2, "^"), Field_Name_in_SQL))
                  where "Field_Name_in_SQL" is the your actual field name.

NOTE:  Use Formula #2 if you are working with historical dates to avoid Daylight Savings Time impact on the conversion.  UTC offset is parsed out and the value is used for the DATEADD parameter.
Powered by Wolken Software