TABLE
site,
work-note,
dateformat(trip-date,"yyyy-MM-dd") as trip-date,
dateformat(trip-date,"ccc") as "day",
trip-confirmed,
dateformat(close-date,"yyyy-MM-dd") as "close-date"
FROM "Events"
WHERE trip-date >= date(today) OR trip-date = null OR close-date = null
SORT trip-date
The result I get is:
Dataview: Every row during final data extraction failed with an error; first 3:
- No implementation of 'dateformat' found for arguments: string, string
I know that the reason is because there is nothing that satisfies the condition of the “where” clause. And I’m assuming that the “dateformat” is failing because it doesn’t know what to do with a null value. I’m not sure about that though.
But I just want to know how I can escape from this error and just have a blank spot, or some other graceful message other than this error. Thanks!
dataformat() expects two parameters, a date and a string. I think the error is telling you that in one of your dateformat() calls, your first parameter is a string, not a date.
I would look closely at your trip-date and close-date field values in your vault. Are they all dates? Most likely one of them is actually a string that looks like a date – perhaps a typo in how the date was entered. You can see them all quickly with a query like the following:
```dataview
TABLE trip-date, close-date
WHERE trip-date OR close-date
```
I agree that the root cause is dateformat() not knowing what to do with null. Dataview has some string it uses for displaying null values, which you can set in the Dataview settings tab. That string is getting passed to dateformat instead of null, I think, but dateformat would be just as grumpy with null.
Solution strategy: use the Dataview helper function choice(...) on your lines where you might be trying to format null. I don’t know whether your condition (first argument) to choice should be checking for null or checking for the string in the Dataview settings tab. The second argument to choice is what to do if your condition is true, third argument is what to do if your condition is false. E.g. for your close-date line of code:
choice(close-date = null, "No close", dateformat(close-date, "yyyy-MM-dd")) as "close-date"
Again, null might not be the right check there, might have already transformed to the string that’s in settings. And you can put whatever you want to display in that condition in place of the sample “No close” string I put in there.
Good luck!
PS: Craig’s advice might still apply, if you continue getting the error after adding in the choice functions.
I tried your solution. However, the error still occurs exactly as before. Trying your solution made me realize something. There actually is no problem with either the “trip-date” or the “close-date” being null. Because if either one of those is null, the query works, and puts a “-” in the corresponding column. That was working fine before implementing your “choice” functions.
What causes the error is when there are no “records” that satisfies the “where” clause – basically when the entire table result is null. Yet, the error says the issue is with the “dateformat” function. So it seems that it is attempting to execute those functions even when there is a null result from the query.
Oh wow that’s fascinating and not at all what I expected. Thanks for posting the correction! I’m really surprised that it puts a “-” in the column without dateformat complaining.
Did Craig’s solution turn up anything? Any dates not formatted as dates?
No, Craig’s solution did not fix the issue. It isn’t a huge issue because it only happens when there’s no result from the query so I know what to expect when that happens. It’s just peculiar that it doesn’t show the normal verbiage when there’s no results from the query as it does with other dataview queries that I have.
If it’s trying to display rows that don’t exist, that sounds like a Dataview bug! Consider filing it as either an Issue or Discussion in the obsidian-dataview github repo? I think the dev is pretty active at answering things there and future users of similar queries will appreciate the fixed error message.