0

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 ]%%

1 Answer 1

3

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.

6
  • Thanks Brad, the SQL worked however I just realised i was missing some key info for the email using the attribute output it would good to use the SystemDateToLocalDate option, any chance you can take a look at the update i made to my original post
    – Andyw
    Sep 10, 2019 at 3:10
  • @Andyw i have updated your script and added to my solution. Sep 10, 2019 at 3:52
  • @Brad-Spakota thanks for that, for some reason Marketing Cloud is returning a error "String was not recognized as a valid DateTime." yet the Data extension field is a date field and i doubled check CRM and that to is set to date field so both are. Any ideas? Might have to run with the SQL option
    – Andyw
    Sep 10, 2019 at 20:24
  • @Andyw are you able to post the date and time field data value and the error screenshot here? Sep 11, 2019 at 2:47
  • @Brad-Spakota Sure, display date/time value from the data extension is 08 September 2019 13:00 Raw data is 08/09/2019 13:00:00 when downloading data extension and opening in notepad - link to screenshot: we.tl/t-zoTmzjxRLM
    – Andyw
    Sep 11, 2019 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.