How do i use multiple AMPScript functions for a single variable?

Question

I have a variable that that is pulling from another variable and applying the Field function to it as below.

set @CampaignStartTime = Field(@CampTypeRows,"Event_Time_Text__c")

I now want to apply the function SystemDateToLocalDate to the line above to make the time output localised.

I tried the below but keep getting validation issues:

set @CampaignStartTime = SystemDateToLocalDate(Field(@CampTypeRows,"Event_Time_Text__c"))

Also, worth mentioning I have another block further down the email which is setting new variables using @CampaignStartTime. Will any changes impact my second block?

   set @GoogleStartEndTime = (CONCAT(
   Format(@CampaignDate, "yyyyMMdd")),
   'T',
   Format(@CampaignStartTime, "HHmm"),
   '00Z',
   '%2f',
   Format(@CampaignDate, "yyyyMMdd"),
   'T',
   Format(@CampaignEndTime, "HHmm"),
   '00Z'   
  )

What am I doing wrong? and How can I fix it?

Answer 1

I suspect, not having your complete Ampscript, the issue is caused by you not selecting a single row, but applying Field function to multiple rows. Here is what you need to do:

SET @rowCount = rowcount(@CampTypeRows) /* count rows to ensure your lookup returns something */

IF @rowCount > 0 THEN /* only execute this block if you have some results */

  SET @row = row(@CampTypeRows,1) /* get row #1 */
  SET @CampaignStartTime = SystemDateToLocalDate(Field(@row,"Event_Time_Text__c")) /* Convert your date */

ENDIF

Your AmpScript further down the page will work without issues.