Compare current time (hours) with time range in Transaction SQL

Question

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.

Answer 1

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 -

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 -

    SELECT
    TimeSent,
    CurrentDate,
    CASE WHEN TimeSent > '07:00PM' and TimeSent < '10:00PM'
    THEN 'TRUE'
    ELSE NULL
    END AS Flag,
    Counter
    FROM
    (
    SELECT
    FORMAT(getdate(), 'hh:mmtt') as TimeSent,
    getdate() as CurrentDate,
    Counter
    FROM
    [Test_DE]
    where 
    Counter = 'Dummy value that was added'
    ) as OUTPUT

3. Create a new DE similar to above, say Test_DE1. Then create new query with overwrite operation as below and insert into Test_DE1 -

    SELECT
    CurrentDate,TimeSent FROM
    [TEST_DE]
    where 
    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)

4. Finally, create an automation as below -

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!!