Dataview query with two WHERE statements

What I’m trying to do

I want to use dataview to list books I completed this year, by querying the properties “book” and “date finished”

Things I have tried

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”.

WHERE type="book"
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:

	type = "book"
	dateformat(date-finished,"yyyy-MM-dd") >= "2023-01-01"
	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.

	dateformat(date-finished,"MM-dd-yy") >= "01-01-23" 
	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):

WHERE type = "book"
WHERE dateformat(date-finished, "yyyy-MM-dd") >= "2023-01-01"
WHERE dateformat(date-finished, "yyyy-MM-dd") >= "2024-01-01"


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.

A note showcasing these date issues

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
WHERE file = this.file
FLATTEN file.lists as L
SORT L.text

## Table with interpretation as dates

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.

1 Like

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