1

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.

2

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.

  • So what are we to do if we want to add an event for the year 11,328? (lol) - thank you for explaining that. I think it may be beneficial to "note to self" some of what I am seeking may be in the "Data Extension" help section rather than AMPscript. Great solution with checking for >12 though kind of a shame as I think it might have been a better data setup to store it as a simple numerical stamp and have a simple function to interpret that to AM/PM even active by default. At least the workaround seems straight-forward. Thanks. – Jonny Shaw Mar 3 '14 at 18:01
  • Kelly, where can I find the above documentation? I'm having trouble locating a section on Data Extensions. – Jonny Shaw Mar 3 '14 at 18:45
  • And in a related detail. I'm seeing that I can use DatePart to extrapolate one detail from a date. I'd guess this works in either direction (review or upsert) but I don't see a "datepart" which addresses the AM/PM indication. So if someone using a form enters 15:00, obviously that means 3pm. How do we express that for the purpose of the upsert? – Jonny Shaw Mar 3 '14 at 19:19
  • You wouldn't be able to update portions, you would have to get the full date, and add the time as 3:00 PM – Kelly J Andrews Mar 3 '14 at 19:30
  • not very convenient… ok, so dates with times will need to be entered as an AM/PM value which of course can be set as GMT if the enter-er chooses and we can decide how to handle that using DATEDIFF or DATEADD to adjust its display or not. I can use if/then's to determine that if there is a PM, adjust the time and display it as (ie.) 12 plus 2. Wow, this could be easier… May need to go back to the drawing board for this. – Jonny Shaw Mar 3 '14 at 20:01

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.