0

Is there a way to customize the default value for a date field in a data extension?

My data extension has a date field, and it is set to the default value to use the current system date. The data extension shows the default value is set to "Current date", however the time of the date stamp is 3 hours behind from the local time of the business unit.

Is there a way to add 3 hours to the default value so it matches with the local time?

1
  • Rather than setting a default value, are you able to just format this value with ampscript/sql/api when you insert the record so that you can normalize the date/time to fit with expectations? Jun 16 '20 at 21:08
1

This is not possible to my knowledge.

I also think it is counterproductive.

Think of your marketing cloud instance like an "onion" with several layers. There is a central "processing core", and around it you build more "processing layers" of tables, scripts, queries etc, as well as one or several outward facing "display layers" (e.g. an email, an export file,...)

At the very core are the data views - tables marketing cloud simply has by default and that you'll need to work with. All dates in the marketing cloud uses in the core are system time (UTC-6) (except for BusinessUnitUnsubscribes, where it explicitly says "UnsubDateUTC")

Around the core, you build a set of data extensions, queries, whatever. Definitely consider Data Extensions that catch & store data your inner "processing layer".

You might very often need the data in here to make date comparisons, calculations etc. with the data stored in the "core". Random example: Compare your stored time with a click date in the data view. Therefore: Leave all your timestamps in the "processing layer" in system time, i.e. consistent with the central infrastructure.

Otherwise: a) you will needlessly complicate all comparison / calculation operations that involves "core" data.

b) you will complicate the act of storing data: in each email send log you build, each SQL you create that should store a timestamp, you'll have to think of transformation, instead of just using the default functions like NOW() or GetDate().

c) Worst case, people operating the system will not even know of the disparity and interpret data wrongly or build inconsistent processes.

d) What if several display processes need different formats, e.g. your next export goes to a system that can only handle UTC? Then all this transformation was for nothing, and you have to revert it back for a specific process again.

So: Once you need the data exposed somehow - consider this your "display layer" - This could be display in an email, on a webpage, exports to other systems, API calls to other systems etc., THEN think about formatting to the desired timeformat using SQL or AMPScript / SSJS to restructure "raw dates" into a desired format like UTC, or UTC-3, or UTC-7 / 8 considering daylight savings,... This is also the first time you have to think about formats like "date with or without time, US or French date format etc." Before that, just use the system standard.

Plus, as a good practice, NAME your date fields explicitly to make clear to every user what he is dealing with.

e.g. call a field in the processing layer "timestampSystemTime" or timestampUTC-6. In your Data Extensions in the display layer (e.g. an export DE) call the timestamps something like "localTime" (if this is clear from your base of operations) or timestampUTC etc.

Hope this made some sense :)

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.