0

The query we've been using for another use case for a few months, is not working on the current automation in which we want to send a journey email to customers who registered on our website in the last 24 hours. The key field for this is Registration_Date (SFMC field type: Date), but for some reason it's not producing results as it is in other automations. Here is how the registration date value example looks like in the data extension: Thursday, July 20, 2021 12:00 AM

The code we are using (worked before, but not in this case):

SELECT UserID, Email, Registration_Date, First_Name, Last_Name
FROM Staging_Journey
WHERE CAST(Registration_Date as date)>CAST(getdate()-1 as date) // we tried also Registration_Date > dateadd(hh,-24,getdate()) //
3
  • 1
    Can you try WHERE convert(varchar, Registration_Date, 0)>CAST(getdate()-1 as date)?
    – zuzannamj
    Jul 21 at 8:54
  • It worked! Thanks! For my understanding, why do we need to convert a field to string since it's already set up as "Date" ?
    – nix9247
    Jul 21 at 9:29
  • I'll add this as an answer and explain
    – zuzannamj
    Jul 21 at 9:29
2

You need to convert your date to a correct format, this this should work:

SELECT UserID, Email, Registration_Date, First_Name, Last_Name
FROM Staging_Journey
WHERE convert(varchar, Registration_Date, 0)>CAST(getdate()-1 as date)

Why do you need to convert it?

The getdate() function returns a date in the following format: Jul 20 2021 12:00AM

While your date is in this format: Thursday, July 20, 2021 12:00 AM

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.