Dataview date to string

What I’m trying to do

Display a table of files where a “date” field sometimes has incomplete or unknown dates (e.g. “1871” as opposed to “1871-02-01”).

Things I have tried

Pretty much everything I could find, including renaming the “date” field to something else and different futile dateformat(date, ...) attempts.

I basically would like my date field to be treated as a string instead of being transformed. I enter my dates in the format yyyy-MM-dd and in order to keep them that way the query has dateformat(date, "yyyy-MM-dd") AS Date.

However, if the date field is incomplete (date: 1857-02), I get 1857-02-01 which is not what I want (the date simply refers to “february”). If the date field only has a year (e.g. 1857), it’s not even included in the table.

How can I keep entering my dates in the format I’m used to (yyyy, yyyy-MM or yyyy-MM-dd) and have dataview treat them as simple strings?

Thanks a lot for your help.

1 Like

More info (sorry, apparently I can’t edit my OP): if I set the incomplete dates to e.g. 1871 for the year and 1871-08--- for a year and a month then all three cases are displayed correctly, but sorting doesn’t work because the two above cases are treated separatelt from the dates. I just need the whole column to be sorted alphabetically, with years and months in the right order (a simple sort). Isn’t that possible?

It’s a lot harder than describing the issue. As you’ve discovered just the year is treated as a number, and the combination year-month and year-month-day is treated as dates, and don’t sort nicely together out of the box.

Here is a somewhat extensive examples showing some alternate declarations, and approaches related to this issue:

---
dYear: 2023
dMonth: 2023-03
dDay: 2023-03-03
sYear: "2023"
sMonth: "2023-03"
sDay: "'2023-03-03'"
fsYear: 2023
fsMonth: 2023/03
fsDay: 2023/03/03
preYear: _2023
preMonth: _2023-03
preDay: _2023-03-03
---

value — dYear: `= this.dYear`, dMonth: `= this.dMonth`, dDay: `= this.dDay`  
typeof — dYear: `= typeof(this.dYear)`, dMonth: `= typeof(this.dMonth)`, dDay: `= typeof(this.dDay)`  
stringified — dYear: `= string(this.dYear)`, dMonth: `= string(this.dMonth)`, dDay: `= string(this.dDay)`  

value — sYear: `= this.sYear`, sMonth: `= this.sMonth`, sDay: `= this.sDay`
typeof - sYear:  `= typeof(this.sYear)` - sMonth: `= typeof(this.sMonth)` - sDay: `= typeof(this.sDay)`

value — fsYear: `= this.fsYear`, fsMonth: `= this.fsMonth`, fsDay: `= this.fsDay`
typeof — fsYear: `= typeof(this.fsYear)`, fsMonth: `= typeof(this.fsMonth)`, fsDay: `= typeof(this.fsDay)`

value — preYear: `= this.preYear`, preMonth: `= this.preMonth`, preDay: `= this.preDay`
typeof — preYear: `= typeof(this.preYear)`, preMonth: `= typeof(this.preMonth)`, preDay: `= typeof(this.preDay)`

Four groups of the combinations:

  • d* – The native, intuitive approach. Returns number, date, date
  • s* - Explicit defined as strings. Returns number, date, date
  • fs* - Using forward slashes to avoid date interpretation. Returns number, string, string
  • pre* - Prefixes with underscore (or any other letter thingy) to avoid numbers and dates. Returns string, string, string

Even specifically casting the first group using string, doesn’t give the raw value, as the month variant, since it’s interpreted as a date will present itself as the first of that month. It’s close to being acceptable in some circumstances though.

So in my experience so far, if you truly want to have those three options of year, year-month and year-month-date, all treated as string, you either need to prefix them with something turning them all into strings _or you need to pick them out of the frontmatter directly. All but the year variant, can still be translated back into strings if need be using either regexreplace() or substring() in combination with date().

Using file.frontmatter gives the raw value

Listed in dataview issue 1403, they talk about possibly adding a rawValue variant to get to the duration directly without it being translated. Furthermore they also talk about lifting the values directly from the _frontmatter, to get to the raw value.

And surely, if your fields are defined in the frontmatter, you can use the following to get to their actual value:

file.frontmatter value — dYear: `= this.file.frontmatter.dYear`, dMonth: `= this.file.frontmatter.dMonth`, dDay: `= this.file.frontmatter.dDay`
file.frontmatter typeof — dYear: `= typeof(this.file.frontmatter.dYear)`, dMonth: `= typeof(this.file.frontmatter.dMonth)`, dDay: `= typeof(this.file.frontmatter.dDay)`

Which will return:

So here we get the combination of number, string, string. In other words, if your fields are defined in the frontmatter, you might use string(file.frontmatter.yourField) to get the raw value as a string.

Thanks. I had indeed ended up writing my dates in a particular way which is still clear to the eye and works nicely for my needs (I am displaying my tables in a monospaced font):

1871 -- --
1871 02 --
1871-02-01

This also has the advantage of further visually distinguishing between precise dates and larger time frames.

It sorts as expected if converting the field to a string when querying:

SORT
	string(date) ASC
1 Like

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