After having tried the last couple of days all sorts of approaches, I hope I can find some help here
What I’m trying to do
How can I extract the calendar week of a date formatted as “YYMMDD”?
I have a number of daily notes, which all start with 6 numeric values, indicating the date (YYMMDD) of the daily note.
In my weekly overview, I would like to see all daily notes in a data view table belonging to the current week based on calendar week.
Example:
Daily Notes:
“230301 - Daily Note”
“230302 - Daily Note”
“230321 - Daily Note”
“230322 - Daily Note”
The first two above belong to calendar week 9 whereas the latter two belong to week 12.
My weekly note, for week 12, should therefore show a Dataview list showing only the last two Daily Notes.
This turns out to be a major challenge (for me - I am certainly not an expert )
Things I have tried
I have tried different approaches:
(1) using dateformat() based on a substring :
TABLE
FROM "1 aws/2 notes/6 periodic notes/2 daily notes"
WHERE dateformat(substring(file.name,0,6),"WW")
(2) Trying to use regexreplace() to insert dashes - but that led me nowhere.
How do I insert a dash in a string, by the way?
(3) I also changed the format of the filename to “YYYY-MM-DD” - no success as well.
aber: ab und an trage ich eine Daily Note nach, eröffne also das File am nächsten Tag oder zwei Tage später (gerade wenn ich auf Reisen bin) und insofern nutze ich nicht file.day, weill das file.day dann nicht stimmt.
Within Dataviewdateformat() is used to present a date, not to read a date, so this won’t work. I’m contemplating on building some function for Dataview to allow it to read a date from string using a defined format, but that’s in the future somewhere.
Adaption related to regexreplace
This however, has potential, and after a little bit of fumbling I came up with this test query:
```dataview
TABLE WITHOUT ID title, textDate,
dateformat(date(textDate), "WW")
FLATTEN list(
"230301 - Daily Note",
"230302 - Daily Note",
"230321 - Daily Note",
"230322 - Daily Note" ) as title
FLATTEN regexreplace(title, "^(\d{2})(\d{2})(\d{2}).*", "20$1-$2-$3") as textDate
WHERE file.folder = this.file.folder AND file.name = this.file.name
```
So if use something like: FLATTEN dateformat( date( regexreplace( file.name, "^(\d{2})(\d{2})(\d{2}).*", "20$1-$2-$3" )), "kkkk-WW" ) as fileWeek you should get a proper thingy like “2023-09” to match agains the current week, like in dateformat(date(now), "kkkk-WW").
Lets collate this into a full query using regexreplace:
TABLE fileWeek
FROM "1 aws/2 notes/6 periodic notes/2 daily notes"
FLATTEN dateformat(
date( regexreplace( file.name,
"^(\d{2})(\d{2})(\d{2}).*",
"20$1-$2-$3" ) ),
"kkkk-WW" ) as fileWeek
WHERE fileWeek = dateformat( date(now), "kkkk-WW" )
Note that using FLATTEN ... as fileWeek allows for it to be included as a column and/or in WHERE clauses. But if you don’t like it it to show as a column you can safely remove it. The table just looked a little empty without it.
A simple explanation on the regex is that matching from the start we pick and store the 3 groups of 2 digits, and then we match against the rest of string just so we can ignore/loose it in the replace. When we replace, we assume we’re in the 21st century, and insert the stored values in the proper places for an ISO8601 date.
This textual date is used in date() to produce a proper date, which is then used in dateformat() to find the week number we’re interested in. Note that I’ve added the year to the format, so it won’t break next year in the same week…
Using file.day
In most case I would favour to have a proper date in the filename, as that would set the file.day to a proper date. If this is done, then the query above could be changed to:
TABLE fileWeek
FROM "1 aws/2 notes/6 periodic notes/2 daily notes"
WHERE dateformat( file.day, "kkkk-WW" ) = dateformat( date(now), "kkkk-WW" )
A much nicer and cleaner solution. If you want to keep your current dateformat in the file title, there is also an option to add the proper date into the frontmatter as date: YYYY-MM-DD, which also would populate the file.day variable, and allow for the query above to be used.
And I reckon you’re already using some template to produce your daily notes, so adding that into the template shouldn’t be too hard. Personally I use a custom format for the date used in the file title which is short whilst still maintaining alphanumerical sorting, where today is N330, but I also add date: 2023-03-30 into the frontmatter so that I can query on file.day.
Final note related to date().week and date().weekyear
When testing related to this I found that there is something askew with week and weekyear in DQL queries. This two should return 13 and 2023 for the current date, but they do return 5 and 13. So the weekyear reports the actual week number, and the week is just way of and reports the week number as if today’s day of month was in january… Might strange. See bug report below for more details.
So for now, don’t use the weekyear nor week but rather do the format as shown above. Within dataviewjs, the actual DateTime object is correct, but the queries output these values strangely.
