DaysDiff function with one date field? | Community
Skip to main content
R
robertw937098
Level 2
September 27, 2018

DaysDiff function with one date field?

  • September 27, 2018
  • 1 reply
  • 4937 views

Hi,

How can I calculate the days difference (using the daysdiff function) on one date field? Preferably for the most recent 3/4 dates in the field.

BACKGROUND: I need to know the days between each customers multiple journey dates for a campaign. Then work out the average of this number.

Many thanks,

Rob

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    1 reply

    Garima_Gakhar
    Adobe Employee
    Garima_GakharAdobe Employee
    Adobe Employee
    September 28, 2018

    Hi robertw937098​

    Date Difference could be calculated as: SELECT DATEDIFF(day, 'yyyy-mm-dd hh:mm:ss', 'yyyy-mm-dd hh:mm:ss');

    PostgreSQL allows you a more accurate difference like (3 days, 10 hours) using the 'timestamp' option as below:

    SELECT 'yyyy-mm-dd hh:mm:ss'::timestamp - 'yyyy-mm-dd hh:mm:ss'::timestamp;

    'DATE_PART' prefixed to the above will again give difference in days.

    SELECT DATE_PART ('day', 'yyyy-mm-dd hh:mm:ss'::timestamp - 'yyyy-mm-dd hh:mm:ss'::timestamp);

    Hope that helps!

      R
      robertw937098Author
      Level 2
      October 1, 2018

      Hi Garima,

      Thanks for your reply.

      Though I still have the same issue. My question is, how is it possible to do a DaysDiff(@OUT_DATE_DEP) function on all the data in one date field per recipient?

      For instance, something like... DaysDiff(row1.@OUT_DATE_DEP,row2.@OUT_DATE_DEP)

      E.g. The below customer has many out date dep dates.

      Many Thanks,

      Rob

      D
      DebTr
      Level 6
      October 24, 2018

      Hi Robert,

      Difference can be found between two fields. But if you are going for difference between all the dates for one Recipient you won't get one output rather many rows. Can you please explain how you want your output to look like?

      Regards,

      Deb

        Terms & ConditionsAccessibility statement

        Loading footer...