Hi you all!
I want to sort my files in a table by a “date” field I usually add in the metadata part as a string in the form of “dd-MM-yyyy” (the date format in the settings). When I try SORT date desc it sorts the files by the number itself, and not by date, so for example 01-09-2022 will be below 10-08-2022.
Any suggestions on how to solve it?
Date format in settings is related with the output of the date, not the recognized ISO format at the source.
https://blacksmithgu.github.io/obsidian-dataview/annotation/types-of-metadata/
This means: to be recognized as dates you need to use the input format “yyyy-MM-dd”
myDate: 2022-08-10
reading-date field into the format like yyyy-MM-dd?dic_19720301---
Date: 1972-03-01
type: literature
reading-date: 31-03-2022
date_format : dd-MM-yyyy
---
dic_19720401---
Date: 1972-04-01
type: literature
reading-date: 20-09-2022
date_format : dd-MM-yyyy
---
dic_19720501---
Date: 1972-05-01
type: literature
reading-date: 21-09-2022
date_format : dd-MM-yyyy
---
dic_19720801 (The month of the reading-date field is greater than 12.)---
Date: 1972-08-01
type: literature
reading-date: 30-76-2022
date_format : dd-MM-yyyy
---
| Code Name | Data type | Group By | Purposes | Remark |
|---|---|---|---|---|
| DQL10 _transform_reading-date _into_yyyy-MM-dd |
reading-date:a string like “dd-MM-yyyy” |
no | 1.To filter by reading-date 2.To define a field variable s_rDate by using FLATTEN 3.To transform the value of reading-date into yyyy-MM-dd 4.To filter by date(s_rDate) 5.To sort by date(s_rDate) in descending order 6.To display the result as a table |
1.ONLY for reading-date field where the value is like “dd-MM-yyyy” 2.The DQL10 is based on the DQL10 in the following topic. - Solutions: by Justdoitcc |
date(s_rDate) in descending order?```dataview
FLATTEN regexreplace(reading-date, "^(\d{2})-(\d{2})-(\d{4})$", "$3-$2-$1") AS s_rDate
WHERE date(s_rDate) != null
SORT date(s_rDate) DESC
```
Limitation: Suppose that the dd number of the
reading-datefield is correct. In other words, it cannot exceed the last day of the month.
```dataview
FLATTEN regexreplace(reading-date, "^(\d{2})-(\d{2})-(\d{4})$", "$3-$2-$1") AS s_rDate
WHERE date(s_rDate) != null
SORT s_rDate DESC
```
Limitation: Suppose that the dd number of the
reading-datefield is correct and the MM number is not greater than 12.
```dataview
FLATTEN regexreplace(reading-date, "^(\d{2})-(\d{2})-(\d{4})$", "$3-$2-$1") AS s_rDate
SORT s_rDate DESC
```
```dataview
FLATTEN split(reading-date, "-")[0] AS dd
FLATTEN split(reading-date, "-")[1] AS MM
FLATTEN split(reading-date, "-")[2] AS yyyy
FLATTEN yyyy + "-" + MM + "-" + dd AS s_rDate
WHERE date(s_rDate) != null
SORT date(s_rDate) DESC
```
```dataview
FLATTEN regexreplace(reading-date, "^(\d{2})-(\d{2})-(\d{4})$", "$3-$2-$1") AS s_rDate
WHERE date(s_rDate) != null
SORT dateformat(date(s_rDate),"yyyy-MM-dd") DESC
```
title: DQL10_transform_reading-date_into_yyyy-MM-dd =>ONLY for `reading-date` field where the value is like "dd-MM-yyyy" 1.To filter by `reading-date` 2.To define a field variable `s_rDate` by using `FLATTEN` 3.To transform the value of `reading-date` into yyyy-MM-dd 4.To filter by `date(s_rDate)` 5.To sort by `date(s_rDate)` in descending order 6.To display the result as a table
collapse: close
icon:
color:
```dataview
TABLE WITHOUT ID
file.link AS "File",
reading-date AS "dd-MM-yyyy",
s_rDate AS "yyyy-MM-dd"
FROM "100_Project/02_dataview/Q78_ddMMyyyy/Q78_test_data"
WHERE econtains(type, "literature")
WHERE reading-date != null
FLATTEN regexreplace(reading-date, "^(\d{2})-(\d{2})-(\d{4})$", "$3-$2-$1") AS s_rDate
WHERE date(s_rDate) != null
SORT date(s_rDate) DESC
```
It solved it, thank you!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
