Troubleshooting dataview birthdays

I’m trying to create a dataview query/table that will show all of my friends and family, with their birthdays in order. So one column with their name and one column with their birthday.

I may also like for it to show them in order beginning with the closest birthdays (so if it’s October, show me October birthdays, then Nov, then Dec, then Jan, rather than starting with Jan,) but I’m highly inexperienced with dataview and just trying to get it working at all, so that’s somewhat of a secondary thing.

Overall, it does seem to be working, so no complaints there. The problem I’m having is rather odd: the birthday column is showing every birthday twice, in two different formats, instead of just once. And the notes themselves only have the birthday listed once, so I really don’t know why.

Here’s my dataview text:

TABLE WITHOUT ID
file.link as "",
birthday as "Birthday"
FROM "📚 Information/📝 Notes/People"
SORT date desc

Here’s a screenshot of how it’s rendering
Screen Shot 2024-10-27 at 7.44.00 PM
As you can see, it says

1970 September 12 Saturday, September 12, 1970

And here’s a screenshot of the note’s properties:
Screen Shot 2024-10-27 at 7.44.43 PM

Maybe there’s something I could add to the query to specify how I want to date to be formatted in the table, but I’m not sure how to do that, and my Google searches came up dry. If anyone has other ideas, of course I’m open to those, as well.

Hi Sophia, as far as I can see, there’s nothing wrong with your dataview query - except maybe you’d want to sort by day and month of the birthday, instead of date.

Concerning the double date… can you have a look into the settings of Dataview plugin? What does the Date format entry look like?

2 Likes

What syntax are you using to store the dates of birth?

```dataview
TABLE WITHOUT ID
file.link as "",
dateformat(birthday, "EEEE, dd MMMM yyyy") as "Birthday"
FROM "📚 Information/📝 Notes/People"
SORT date desc
```

1 Like

For the task to put your output in correct order here is an example (with German date formatting from my sandbox vault, which doesn’t affect the dataview query though):

grafik

So in your example this would read:

TABLE WITHOUT ID
file.link as "",
birthday as "Birthday"
FROM "📚 Information/📝 Notes/People"
SORT birthday.day asc
SORT birthday.month asc
2 Likes

Not exactly what you want, but the query below should list birthdays left for the current year when judged against the current day’s date, so long as the birthday property is an ISO-recognized date: yyyy-MM-dd.

```dataview
TABLE WITHOUT ID
file.link as "",
dateformat(birthday, "EEEE, dd MMMM yyyy") as "Birthday"
FROM "📚 Information/📝 Notes/People"
FLATTEN dateformat(birthday, "MM-dd") as bdate
WHERE bdate > dateformat(date("today"), "MM-dd")
SORT bdate ASC
```

Considering @anon45210282 's suggestion you could just use two tables, here I made an effort without FLATTEN:

Adopted to your data:

TABLE WITHOUT ID
file.link as "",
birthday as "Birthday"
FROM "📚 Information/📝 Notes/People"
WHERE birthday.month > date("today").month OR (birthday.month = date("today").month AND birthday.day >= date("today").day)
SORT birthday.day asc
SORT birthday.month asc
TABLE WITHOUT ID
file.link as "",
birthday as "Birthday"
FROM "📚 Information/📝 Notes/People"
WHERE birthday.month < date("today").month OR (birthday.month = date("today").month AND birthday.day < date("today").day)
SORT birthday.day asc
SORT birthday.month asc
1 Like

Correct the formatting

It seems like you’ve done something strange in your settings for displaying dates within Dataview. This can be handled in two ways, either through doing something like dateformat(birthday, "DDD") where you pick the tokens for the format string from the list shown here: luxon - Immutable date wrapper. If you want to use that format in general, you can also set just once in Settings > Dataview > View Settings > Date Format. Use the same token for the format string there too.

Using the ordinal day of year

Regarding the second part of displaying birthdays later in the year, before birthdays which have already been this year, you could translate the date into the ordinal day of the year, and then introduce a choice() function to add a year to those which have already been. This can be done in multiple ways, like in the following:

```dataview
TABLE WITHOUT ID 
  file.link as "",
  dateformat(birthday, "DDD") as Birthday
FROM "📚 Information/📝 Notes/People"  
FLATTEN number(dateformat(birthday, "o")) as oday
SORT choice(oday >= number(dateformat(date(today), "o")), oday, oday+365) 
```

Adapt the date format string to your liking, or set the general option as described above.

A wider more explanatory query
```dataview
TABLE WITHOUT ID 
  file.link as "",
  birthday as Birthday
  , ordinal - number(dateformat(date(today), "o")) as "Days until"
  , oday
  , dateformat(date(today), "o")
  , ordinal
  
FROM "📚 Information/📝 Notes/People"  
FLATTEN number(dateformat(birthday, "o")) as oday
FLATTEN choice(oday >= number(dateformat(date(today), "o")), oday, oday+365) as ordinal
SORT ordinal
```

In this script in addition to storing the ordinal day of the birthday as oday, I also store the newly calculate ordinal value in ordinal, and display all of the various values in the table. This also allows us to calculate how many days until the next birthday…

The key to this query is that depending on whether the day in year of the birthday is larger than the day in year of today, it either keeps the ordinal value, or adds 365 days (as in the birthday next year).

Date manipulation

In the previous query we always add 365 days to get to the next year. Another version is to actually do date manipulation to correctly account for leap year and so on, and maybe it reads a little easier too? Now the query looks like this:

```dataview
TABLE WITHOUT ID 
  file.link as "",
  nextBirthday,
  birthday as "Born",
  nextBirthday.year - birthday.year as "Age"
FROM "📚 Information/📝 Notes/People"
FLATTEN date(dateformat(birthday, date(today).year + "-MM-dd")) as thisYearBirthday
FLATTEN choice(thisYearBirthday > date(today), thisYearBirthday, thisYearBirthday + dur("1 year")) as nextBirthday
SORT nextBirthday
```

And with a format setting for the date to be the ISO8601 format this results in this table:

An explanation of the query is as follows:

  • In the first FLATTEN I create the date of this year with the day and month from the birthday, and store it in thisYearBirthday. This is a date which might have passed already, but we tackle that in the next line
  • If this years birthday has been, we add a year to the date and stores this in nextBirthday.
  • Finally we sort on when the next birthday is

In the display section I display both the next birthday, and the original birthday, and calculate the age they will be at the next birthday. If you want you could also add nextBirthday - date(today) as "Days until" as another column to display how far away the next birthday is…


Sorry for the lengthy answer, you could say I got a little carried away… :smiley:

3 Likes

Between @Hampton 's suggestion for dateformat and @Mephi 's suggestion for the two different tables (one showing upcoming birthdays in the year and one showing all birthdays for the following year), it’s now doing exactly what I wanted. Thanks, everyone, for the input!

More details for anyone reading this in the future:

This is the post that helped me fix the date format:

And this is the post that helped me get the birthdays in the order I wanted:

2 Likes

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