dateformat not showing expected results.

[mayankkalbhor]

I have this markdown file: events.md

event:: Autumn starting | 2022-09-03
event:: Winter starting | 2022-12-07
birthday:: My birthday | 2022-12-16

then on another markdown file testingevents.md i am running the below dataview query:

TABLE WITHOUT ID 
    split(sub, "\|")[0] AS "Event", 
    dateformat(date(split(sub, "\| ")[1]), "MM-dd-yyyy")  AS "Date"
WHERE 
    event FLATTEN event AS sub SORT date(split(sub, "\| ")[1]) 
ASC

And i am able to see the results in the table.

But my requirement is i only want to display events in September 2022. So i tweaked the query as below:

TABLE WITHOUT ID 
    split(sub, "\|")[0] AS "Event", dateformat(date(split(sub, "\| ")[1]), "MM-dd-yyyy")  AS "Date"
WHERE 
    dateformat(date(split(sub, "\| ")[1]), "MM-dd-yyyy") <= date(09-31-2022)
FLATTEN event AS sub

but the above is not working. And giving me error

Dataview: Every row during operation 'where' failed with an error; first 3:

                - No implementation of 'date' found for arguments: number
- No implementation of 'date' found for arguments: number
- No implementation of 'date' found for arguments: number

Does anyone know how to resolve this. I did a lot of trials but still not able to achieve this

[AB1908]

This doesn't work because date gives you a Date object whereas dateformat gives you a string. Rule when comparing dates: either compare after making both sides strings or both sides dates. Anything else fails.

This has happened often enough that we should add it to the docs. Can you open an issue and assign it to me?

[mayankkalbhor]

Sure will raise it.

I tried this also but giving same error.

dateformat(date(split(sub, "\| ")[1]), "MM-dd-yyyy") <= dateformat(date(09-31-2022))

[AB1908]

Almost there, need to use ISO dates, not that format you're using there: 2022-09-31

[mayankkalbhor]

I tried to make it ISO. The table is showing now but its bringing in December values as well

TABLE WITHOUT ID 
    split(sub, "\|")[0] AS "Event", dateformat(date(split(sub, "\| ")[1]), "yyyy-MM-dd")  AS "Date"
FROM "events"
WHERE 
    dateformat(date(split(sub, "\| ")[1]), "yyyy-MM-dd") < dateformat(date(2022-09-31), "yyyy-MM-dd")
FLATTEN event AS sub

[AB1908]

Can you paste the metadata for notes that are from December/are unexpected? It's most likely some sort of error in either the condition or in the metadata.

[mayankkalbhor]

I am not using metadata. This is the content of the note events.md:

event:: Autumn starting | 2022-09-03
event:: Winter starting | 2022-12-07
birthday:: My birthday | 2022-12-16

[AB1908]

Oh event is an array. That was why. Do the flatten first then run the where.

[mayankkalbhor]

wow this is great. Yes moving flatten above where worked well. Thanks a lot @AB1908