I want to use dataview to list books I completed this year, by querying the properties “book” and “date finished”
I’m sure I’m way off, but this is the best I’ve come up with. I get an “expected variable” error just before “date finished”.
List
WHERE type="book"
AND
WHERE date-finished > 01-01-23 AND < 12-30-2023
Thank you!
If you have in your notes something like
---
type: book
date-finished: 2023-12-15
---
You can set up the date range like this:
```dataview
list
where
type = "book"
and
dateformat(date-finished,"yyyy-MM-dd") >= "2023-01-01"
and
dateformat(date-finished,"yyyy-MM-dd") < "2024-01-01"```
Thank you for the suggestion! But it says “no results to show for list query”.
I’ve tried this query below too, and still can’t get any results. (I verified the LIST FROM is correct.)
Note: I use the date format MM-dd-yy in my YAML.
LIST FROM "007 MEDIA 💿/BOOK NOTES 📚"
WHERE
dateformat(date-finished,"MM-dd-yy") >= "01-01-23"
and
dateformat(date-finished,"MM-dd-yy") < "01-01-24"
You’re tackling two different issues, if I read your request correctly. One related to how to combine WHERE and AND, and another one related to date formats.
Both of the following are equivalent and means the same (and are correctly formatted):
```dataview
LIST
WHERE type = "book"
WHERE dateformat(date-finished, "yyyy-MM-dd") >= "2023-01-01"
WHERE dateformat(date-finished, "yyyy-MM-dd") >= "2024-01-01"
```
or
```dataview
LIST
WHERE type = "book"
AND dateformat(date-finished, "yyyy-MM-dd") >= "2023-01-01"
AND dateformat(date-finished, "yyyy-MM-dd") >= "2024-01-01"
```
Having multiple WHERE clauses are just AND’ed together, but you cant do the WHERE ... AND WHERE ... that is a syntax error.
Regarding the date issue, if you write your date-finished as 11-30-2023, it’s not considered as a date by dataview. It’s just a text resembling a date. As such, the dateformat() will not do anything as it doesn’t get a date input. And even if it managed to interpret it as a date, you can’t rely on alphanumeric sorting (and filtering) with the format “MM-dd-yy”.
Consider the following sequence: “01-01-20”, “01-02-19”, “12-01-23”, “12-02-21”. This is sorted in alphanumerical correct order, but interpreted as a dates it’s way off…
So the better option is to change your dateformat (as written in the YAML) to yyyy-MM-dd to make it correctly be interpreted as a date, which also can be used in alphanumerical sorting/filtering.
Alternatively, you could use the date(text, format) to reformat the string into a date, and then use date functions again to get your wanted result.
To showcase these issue, put the following into a file of its own, and switch to reading view.
Some dates in the `MM-dd-yy` format:
- 01-01-20 - 1st Jan, 2020
- 01-02-19 - 2nd Jan, 2019
- 12-01-23 - 1st Dec, 2023
- 12-02-21 - 2nd Dec, 2021
## Queried as is
```dataview
LIST WITHOUT ID L.text
WHERE file = this.file
FLATTEN file.lists as L
SORT L.text
```
## Table with interpretation as dates
```dataview
TABLE WITHOUT ID aDate, L.text
WHERE file = this.file
FLATTEN file.lists as L
FLATTEN dateformat(date(substring(L.text, 0, 8), "MM-dd-yy"), "yyyy-MM-dd") as aDate
SORT aDate
```
It should display something like:
Bravo! Biting the bullet and changing my date format to yyyy-MM-dd did the trick. Thanks so much!
Nice! remember to mark a response as a solution to guide others in the future.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
