0

I'm in the process of setting up a triggered automation that follows the process Upload to FTP > Import > Wait > Query > Wait > Send Email (to the target data extension of the query). I'm currently using the following simple query to dump all records created today into a data extension:

SELECT * FROM SCAAll
WHERE CreatedDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

CreatedDate is set in the data extensions to populate the current date by default - this isn't dictated by the import that happens prior to the query. All of the imported records show today's date as the CreatedDate. For some reason, the records don't make it into the target data extension. Any ideas why this might happen? An issue with the query, or the date field itself? Let me know if I can provide any more information.

Improve this question
2
  • Is this fired from a trigger? Is it a before insert trigger?
    – SeanGorman
    Jul 30, 2015 at 16:09
  • It's using the "triggered" setting in the Automation Studio - meaning it's listening for a file upload to the FTP. When it detects the file, it imports it to a data extension, waits, runs the query, waits again, and then sends the email.
    – ndamelio
    Jul 30, 2015 at 16:11

1 Answer 1

Reset to default
2

Although the data type in Data Extension is labeled Date, what is being stored in the underlying database is a Date and Time value. The Current Date option will record the Data and Time the record is inserted relative to the database server time.

You should be able to modify your query slightly in order for it to return a recordset. You can either cast the createDate value in your where condition to a Date data type; or you can have the createDate compared against a date range.

Here's an example of the latter option, which queries records with a createDate on or after 12:00AM today and before 12:00AM tomorrow.

SELECT * FROM SCAAll
WHERE CreatedDate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
AND CreatedDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
Improve this answer

You must log in to answer this question.

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