How to use Ampscript to correctly display date to match correct country date/time or update SQL to save data in correct time zone

Question

We are pulling data into a Marketing CLoud Data Extension from Salesforce CRM using a SQL Query (from a Salesforce Campaign). The issue we have is the date data that is pulled through is US date/time however we are in New Zealand so when we output it into an email is shows the wrong date/time(16hrs out).

Is there a easy way to convert the date into NZ time using Ampscript or ideally at a SQL level it converts the date to New Zealand date/time?

The Data Extension name is FMPCampaign Field we are using is StartDate

The email is being sent to Data Extension A, the Start Date is being pulled from Data Extension B

Code we have in the email to get the data from the Data Extension B is

%%[ SET @rows = LookupRows("FMPCampaign_Data" ,"Status", 'Open') IF RowCount(@rows) > 0 THEN SET @row = Row(@rows,1) SET @price = Field(@row,"Net_Price__c") SET @StartDate = Field(@row,"StartDate") ENDIF ]%%

Answer 1

Using AMPScript:

You can achieve this using the SystemDateToLocalDate AMPscript function. Basically this function converts the date and time to the current local time.

For example:

StartDate: %%=SystemDateToLocalDate(AttributeValue('StartDate'))=%%

Updated script:

%%[ 
SET @rows = LookupRows("FMPCampaign_Data" ,"Status", 'Open') 
IF RowCount(@rows) > 0 THEN 
    SET @row = Row(@rows,1) 
    SET @price = Field(@row,"Net_Price__c") 
    SET @StartDate = Field(@row,"StartDate")
    SET @StartDateLocal = SystemDateToLocalDate(@StartDate)
ENDIF ]%%

StartDate: %%=v(@StartDateLocal)=%%

Using SQL:

The second option is to use SQL to add 16 hours to the date and time column (StartDate).

For example:

SELECT DateAdd(hh,16,StartDate) AS StartDate
FROM FMPCampaign

Note: Using the SQL method doesn't settle during the daylight savings. If you're going to output the date and time in an email, using the AMPScript is recommended.