[SalesForce] ExactTarget Date and Time guidelines in AMPscript

Want to address the fact that Kelly's answers below did get me to the point of resolving this date/time challenge within AMPscript and ExactTarget. There was considerable effort involved in mastering the dateStamp and being able to isolate different details within our Landing Page Input form but with patience and time it works and will allow our users to easily isolate dates and times. I hope this helps others.

This is a followup to a previous question regarding date and time pickers for an ExactTarget AMPscript administrative landing page. The challenge today is of mastering the parsing of DateTime stamp into multiple fields that will allow a user to use one form field to set the date and another to set the time, then concatenate the string for an upsert into a single "startsAt" or "endsAt" data extension (DE) field. I've had some success using DatePart to isolate the details but have noticed that when a datetime stamp is saved into the ExactTarget DE the format (visible through ET) is (ie. 2/25/2014 2:01 PM) It's pretty easy for me to see where the dd/mm/yyyy hh:mm a/p reside but more challenging to master this in terms of upserting and decoding into separate variables. An additional consideration is that our company uses a 24 hour GMT clock for timed events so instead of 04:00 PM we want this displayed as 16:00.

I have reviewed DateTime AMPscript Functions page on ET but I feel like it's not addressing some structural details of how datetimes are stored and retrieved. Any suggestions that might simplify this are greatly appreciated. Currently I'm attempting to use an @startsAt variable to represent the complete field of "StartDate" in the data extension, then using DatePart(@startsAt, "d") etc. to parse the various parts of the date into unique variables and then using concat(@startDay, @startMonth, @etc.) to combine them into a single variable but it appears I'm missing something that will turn this back into a system aware datetime stamp which would also allow the choice between a 12 or 24 hour clock.

Best Answer

The documentation on the data extension data types reads:

Date

  • A system date
  • You can use an AMPscript function to format this value when you present it in a message
  • Attributes of a date type will accept dates in various formats, such as 1/1/2005 or January 1, 2005
  • All dates are stored in the format MM/DD/YYYY, and if a date is entered in a different format, the system will convert it to the proper format when storing it
  • Valid date values begin after 12/31/1772 and before 01/01/10000
  • You can add a HH:MM time value following the date value, such as 12/12/2012 12:12 PM.

As long as your data extension uses the data format for the column, and your string is in the proper format according to the above guidelines, you should be ok.

It appears that we do not, however, store as a 24-hour time (which btw is not GMT per se, unless you are not using a timezone offset). A simple fix for this would be to check for AM/PM and add 12 when parsing from the DE, and check for >12 and modify when going back to the DE to store.

To use Concat() to pull individual items together, the string would have to match the above valid date format. 

CONCAT(@month,"/",@day,"/",@year," ",@hour,":",@minute," ",@ampm)

This will produce a valid string, especially when using DatePart(), since it returns a string of the segment you requested.

Related Topic