Querying year in a date field

What I’m trying to do

Hello I have a bunch of notes that I use as a reading list. Besides obvious fields (author, title, year of publication…), each note as a “finished” field with the date in this format “2022-06-15”.
What I want to do is to get a table with all the books I’ve read in 2022 so I guess I want to filter all those books that in this date field contain “2022”

Things I have tried

As suggested here I’m trying to filter this date field like this:

	order as Order,
	status as Status,
	"![|60](" + cover + ")" as Cover,
	link(file.link, title) as Title,
	author as Author,
	join(list(publisher, publish)) as Publisher,
	genre as Genre,
	finished as FINISHED
FROM #📚Book
**WHERE finished.year = "2023"**
SORT order ASC, status DESC, file.ctime ASC

This doesn’t work. If I put this code it works:

WHERE finished.year = date(now).year

but I’d like to be able to have the table returning books read during 2022 so, how can I calculate “date(now).year - 1 year”?
Any help appreciated :slightly_smiling_face:

I realized that the solution (below) was nonsensical for what I wanted as I was working with relative dates…while I need specific year that doesn’t change if the query is run next year :roll_eyes…so how can I get something like:

WHEN finished (YAML date field like "2022-03-10") contains year "2022" 

EDIT: That’s the solution for relative dates in case could help someone…

I could get it, thanks to a post from @Moonbase59 :slightly_smiling_face:

WHERE finished.year = (date(now) - dur(1 year)).year
1 Like

This works locally, based on a sample YAML field of:

finished: 2022-02-13

This is the where clause I used:

WHERE finished.year = 2022

Is that what you want?

Yes! Thanks! I made a lot of attempts but not this one I guess… :upside_down_face:

1 Like

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