I need to capture the time of a file drop in an automation in SFMC in hours. So, I think I will have to capture time stamp as now() in hours.

Then I have to compare the current hour to a time range, e.g. between 7pm and 10pm (19:00 and 22:00).

If the current time is within the time window create a record in a DE, if not create 2 records or overwrite the DE with 0 records.

Any ideas how I can do it with a query with 2005 transnational SQL in SFMC? The queries need to be in a syntax similar to Ms SQL Server 2005 that works with Salesforce Marketing Cloud.

Thank you in advance.


In order to achieve this using Queries, you will have to divide these into multiple steps as following -

  1. Create a DE, say Test_DE and include the following fields - enter image description here

  2. Add a dummy value to the Counter field of the DE and save it. Then Create a Query with the update operation as below and insert into Test_DE -

    CASE WHEN TimeSent > '07:00PM' and TimeSent < '10:00PM'
    END AS Flag,
    FORMAT(getdate(), 'hh:mmtt') as TimeSent,
    getdate() as CurrentDate,
    Counter = 'Dummy value that was added'
    ) as OUTPUT
  1. Create a new DE similar to above, say Test_DE1. Then create new query with overwrite operation as below and insert into Test_DE1 -
    CurrentDate,TimeSent FROM
    Flag = 'TRUE'

If your file drop had started during the time period specified in step 2 query, then Test_DE1 will contain 1 record. (Note - this is also applicable, if 2 file drops happen)

  1. Finally, create an automation as below - enter image description here

In the third step of the automation, you have to select the DE(Test_DE1) and include condition if count is equal to 1. If it contains a record then it will proceed to next steps where you can write the queries to insert your data into specific DE's. Else, if record count is 0, then it will stop at step 3 of the automation.

Hope this helps!!

| improve this answer | |
  • Thank you very much Akash. That is great :) Very clear. Thank you for your help. – Inna Carvajal Jul 1 at 1:03
  • Hi Akash, may i ask if [TEST QUERY] = Test_DE? so, if the time period is '07:00PM' and TimeSent < '10:00PM' - we will have 1 record, if not, none. Is it correct? – Inna Carvajal Jul 2 at 15:40
  • If the automation will be reoccurring, how do we check the Test_DE daily? it will accumulate True records, right? – Inna Carvajal Jul 2 at 15:46
  • I have corrected the DE in 2nd query. Yes, if it is within the time period, the second step of automation(Test_DE1) will contain 1 record, else 0. You can check the flag value in Test_DE, if true, then file drop occurred within the specified time. – Akash R Jul 3 at 15:38

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.