I’ve been trying to get a dataview query to show yesterday’s unchecked tasks to show up in tomorrow’s daily notes. Similar to what is in this post here, Unfinished tasks from yesterday. I know the term yesterday is relative and not absolute but nothing I’ve tried has worked. I think it might be a date formatting issue. This is what I’d like for it to look like, minus the checked boxed of course.
To-Do’s
Priorities
Review finances 2024-03-25
Obsidian setup
Work VM Setup 2024-03-25
Nice To-Do’s
PD courses to take and outline
Things I have tried
I’ve created three different daily notes with 3 consecutive dates and various checked and unchecked boxes but the only thing that shows is the above error or it’ll show the absolute yesterday and not the relative yesterday to the date in that daily notes title.
TASK
FROM "Reviews\00 - Brain Dump"
WHERE !completed
AND file.day = date(ddd-MM-DD-YYYY) - dur(1day)
The above shows the ‘Dataview: No results to show for task query.’ response.
TASK
FROM "Reviews/00 - Brain Dump"
WHERE file.day = date(today) - dur(1day)
AND !completed
GROUP BY "Yesterday's To-Do's"
While this query just shows the absolute yesterday to the daily note and not the relative. I’ve taken out the “- dur(1day)” before and it shows today’s unchecked to-do’s but I can’t figure out a way for the - 1 day to work. Any help would be appreciated, thanks.
I don’t think you understood what I asked for; in your files do you have a property called date using the ISO8601 format, i.e. “2024-03-25”? Or do you just have the file name typically similar to Mon-03-25-24?
Assuming the latter, you might try something like:
```dataview
TASK
FROM "Reviews\00 - Brain Dump"
WHERE !completed
AND date(substring(file.name, 4), "MM-dd-yy") >= date(today) - dur(1 day)
```
If you had the property set, so that file.day holds a value, you should be able to do something like:
```dataview
TASK
FROM "Reviews\00 - Brain Dump"
WHERE !completed
AND file.day >= date(today) - dur(1 day)
```
The first query uses date(text, format) to transform your text back to a date, but since it’s not too fond of localized dates we skip that part of the date using substring().
Okay, I think… I understand what you’re saying. The property date in the YAML that I would set when a daily notes opens is in the mm/dd/yyyy format ISO8601 format, “yyyy-mm-dd.” But the file name above that is formatted to be “ddd-MM-DD-YY” whenever it opens. So, your first solution:
TASK
FROM "Reviews\00 - Brain Dump"
WHERE !completed
AND date(substring(file.name), "MM-dd-yy") >= date(today) - dur(1 day)
I tried the following:
TASK
FROM "Reviews/00 - Brain Dump"
WHERE !completed
AND date(substring(ddd-MM-DD-YY), "YYYY-MM-DD") >= date(today) - dur(1 day)
GROUP BY "Yesterday's To-Do's"
and received this error:
Dataview: Every row during operation 'where' failed with an error; first 3:
- No implementation of 'substring' found for arguments: null
- No implementation of 'substring' found for arguments: null
- No implementation of 'substring' found for arguments: null
The second solution still gave me this:
Dataview: No results to show for task query.
I did a typo in my response, and have since corrected it, but this: date(substring(ddd-MM-DD-YY), "YYYY-MM-DD") and this date(substring(file.name, 4), "MM-dd-yy")` is very different…
The first one, produced by you, tries to pull a substring from a field called ddd-MM-DD-YY which would most likely not exist, and therefore be null, and then it tries to extract a date using the “YYYY-MM-DD” format. This is going to fail… Hard…
The other one, is extracting the non-localised part of a file name like “Mon-04-02-24” using substring(file.name, 4) so that we’re left with the 04-02-24. Then it uses date(text, format) to try to figure out a proper date from this, and it tries to match it against the format of "MM-dd-yy" which is the correct date format for the text we’ve extracted. The reason this should work, is that the extracting dates from strings are a little iffy when it comes to localized date format, so by removing the weekday bit, it’s a lot safer to use.
So my updated example in my previous example, using the latter variant presented here, should work as it transforms the file name into a proper date which can be compared to the date of today where you subtract a given number of days.
But please be diligent when copy example queries, as the details are very important and can drastically alter the meaning of the query, as illustrated by your changes done here. And yet again, sorry for my initial typo in the query where I left out the , 4) part of the call to substring().
Right, well. I feel I should mention I’m still fairly new to Obsidian and programming languages. Thank you for having such great patience with me and my queries.
So, I tried the script you provided and it did work… kind of. See I changed the date format from “ddd-MM-DD-YY” to “MM-DD-YY-ddd” because I noticed the dates weren’t in order in the sidebar if I had the day listed first as opposed to at the end. The code you provided did work for the original date format I had but since I’ve changed it I get a new error.
Dataview: Every row during operation 'where' failed with an error; first 3:
- Can't handle format (MM-dd-yy) on date string (04-07-24-Sun)
- Can't handle format (MM-dd-yy) on date string (04-07-24-Sun)
- Can't handle format (MM-dd-yy) on date string (04-07-24-Sun)
I realize now that the (file.name, 4) was so that the first 4 bits were taken off of the date but I can’t figure out how to remove the last 4? Also, when I did have the date format as it was originally in the ‘ddd-MM-DD-YY’ it was showing the results I wanted but only the relative yesterday not the yesterday relative to the date in the file’s title.
The better way to name your files is using the ISO 8601 date format of YYYY-MM-DD, but I do understand that many don’t like it. The second best case is then to use date: YYYY-MM-DD as a property within your files. If either of these are present you’re able to use file.day in your queries as a proper date.
If neither is used, you need to extract the correct part of your file name, and re-read the date out of that text. So some examples of that could be, where the first character of a string is at position 0:
substring("mon-01-02-03", 4) will return 01-02-03
substring("01-02-03-mon", 0, 8) will return 01-02-03
Either of these could then be used as input to date( ..., "MM-dd-yy") to produce a proper date.
So given your file.name as MM-DD-YY-ddd which ends up like 04-12-24-Fri, you’ll need date( substring(file.name, 0, 8), "MM-dd-yy" ) to convert it back into a date.
Note that this will sort correctly until you pass into the next year, where it’ll be skewed again. To get proper sorting based on the text variant of a date, you’ll need to use numbers with the year first, then the month and then the day of month. Aka ISO 8601 with either two or four digits for the year.
If already converted into a date, or using file.day the sorting should sort itself out (pun intended ).
2024-03-25
).