Salesforce: Passing Dates

Date fields in Salesforce accept the following common date formats. This field supports the YYYY/MM/DD format, as well as the following variations that include a time stamp as well:

• YYYY-MM-DD
• YYYY-MM-DD hh:mm:ss
• YYYY-MM-DDThh:mm:ssZ
• YYYY-MM-DDThh:mm:ss.sssZ

The "Z" noted at the end of the final two formats corresponds to the appropriate time zone that the hours/minutes/seconds corresponds to.

The Alchemer Date question has a validation option to format dates in the format that Salesforce accepts.

If you wish to use one of the other formats you can do so and then use a script to alter it to be in YYYY/MM/DD format, before sending it to Salesforce. 

Using a Script to Re-format Dates to Send To Salesforce

To get started set up the date field you wish to transform. In most cases this will be a the Date question using either MM/DD/YYYY or DD/MM/YYYY validation but it could also be a hidden value that is storing a date that is not formatted to send to Salesforce. In the example script we are asking a date of birth (DOB) question. 

Next, add a Custom Script Action to your survey and paste one of the below scripts depending in the format of your date question. 

LUA Script to Transform MM/DD/YYYY formatted dates to YYYY/MM/DD

dobid = 2 --Date of Birth Question ID#

id = 9 --Hidden Value question ID # to send to Salesforce
originaldob = getvalue(dobid)
month = string.sub(originaldob,1,2)
day = string.sub(originaldob,4,5)
year = string.sub(originaldob,7)
newdate = year .. "-" .. month .. "-" .. day

setvalue(id,newdate)

LUA Script to Transform DD/MM/YYYY formatted dates to YYYY/MM/DD

dobid = 2 --Date of Birth Question ID#

id = 9 --Hidden Value question ID # to send to Salesforce
originaldob = getvalue(dobid)
day = string.sub(originaldob,1,2)
month = string.sub(originaldob,4,5)
year = string.sub(originaldob,7)
newdate = year .. "-" .. month .. "-" .. day

setvalue(id,newdate)

There are specific items that you must update for each survey, and specific items you can change depending on the situation. 

Required Updates

dobid = 2 --Date of Birth Question ID#

The ID number listed in this line is the ID of the question (or possibly hidden value) where you are gathering the date you need to convert to YYYY/MM/DD. Replace 2 with whatever the ID

id = 9 --Hidden value ID# to send to Salesforce

The ID number listed in this line is the ID of the hidden value where the script will store the new YYYY/MM/DD formatted value so that it will pass to Salesforce properly.

Lastly, inside your Salesforce push action, make sure to choose the new hidden value that stores the re-formatted date in the Alchemer Question field.

Formatting System Date

If you're just passing the system date and you just need to make sure it's formatted properly.

The system date merge code [system("date")] will display the current date (in the US Eastern timezone). Using the format attribute you can reformat this to a format the Salesforce accepts. Depending on your integration you may need to use a dash instead of a forward slash. 

[system("date"),format="Y/m/d"] will output: 2012/11/06

[system("date"),format="Y-m-d"] will output: 2012-11-06