3

I would like to know when each record in my Data Extension was inserted.

Is there is a system level way of doing that?

I'm aware that I can add a field to do this that will add the system date at the point the record is written. I'm just wondering if I can access this data for an old Data Extension that doesn't contain such a field.

Improve this question
1
  • 3
    You have to have a field on the DE that handles this, there is no system field to be used
    – EazyE
    Commented Feb 26, 2021 at 18:01

2 Answers 2

Reset to default
10

If your data extension has a retention time, then each row gets a hidden field that you can simply query with SQL or AMPScript: _CreatedDate. At least in query studio you have to relabel the field to get a result, as "normal" DE fields aren't allowed to start with an underscore.

SELECT 
testField, 
[_CreatedDate] AS myCreatedDate
From yourDEWithRetention

My suspicion is that it's used in how record level data retention time is calculated internally, as it only works in DEs with retention.

Improve this answer
2
  • 1
    This is fantastic! Exactly the kind of answer we want to see on this board.
    – Timothy
    Commented Mar 1, 2021 at 15:47
  • 1
    As a note on this as there is some misinformation being spread, although you can add retention to an existing DE that will create this field in the DE, the field will only show the current time that you added the retention as the created date, not the real date the record was created. So you will need to have the retention set before the record is added to have the true created date value in this field. Also it needs to be retention on individual records to have this field created.
    – Gortonington
    Commented Mar 3, 2021 at 19:20
4

There is no way to access the record level created date without using the trick you mentioned around the date field on the DE. Documenting below for those that aren't sure how to accomplish that.

By selecting the "use the current system date" option on the field attribute.

For example, while creating the DE, you can select it here:

DE Creation If you have an existing DE, you can edit the attribute and select the option:

Editing an Attribute

This will result in the system date being written as the default value each time a new record is added:

Record View

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .