Sort CRM DataView by date closest to today but not past

What I’m trying to do

I’m trying to create a contact list that sorts by next birthdate. I want the query to return the nearest date after today so that the next person’s birthday is always on top

Things I have tried

1 Like

Have you tried anything? Do you have a query listing your birthdays in the right order?

Yup, able to sort by birthday, but it sorted by year:

TABLE birthday
FROM "Contacts"

SORT birthday ASC

Name March 05, 1983
Test February 26, 1990
Name January 29, 2020
Name January 23, 2022

Even if I could filter out the year and have just each month, I could then sort ASC and adjust the query each month, but if there is an automated way to do this, that is ideal

Found this and it seemed to work

LIST birthday
WHERE birthday.month = date(now).month

You’ve got two options for sorting on the day and month part of a date, and that is either SORT birthday.month, or by doing a dateformat first, and then do SORT dateformat(birthday, "MM-dd").

To get just the first after today, we can sort and apply a LIMIT 1 to the result. So something like the following should do the trick:

TABLE birthday, dayAndMonth
FROM "Contacts"
WHERE birthday
FLATTEN dateformat(birthday, "MM-dd") as dayAndMonth
WHERE dayAndMonth > dateformat(date("today"), "MM-dd")
SORT dayAndMonth

I’ve only included contacts actually having a defined birthday, and used FLATTEN ... as dayAndMonth to do the formatting of the birthday just one time.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.