Topic
Summary
- How to transform the value of the
reading-datefield into the format like yyyy-MM-dd?
Test
Summary
- dataview: v0.5.46
Input
Summary
dictionary files
- Location: “100_Project/02_dataview/Q78_ddMMyyyy/Q78_test_data”
folder: 03
- filename :
dic_19720301
---
Date: 1972-03-01
type: literature
reading-date: 31-03-2022
date_format : dd-MM-yyyy
---
folder: 04
- filename :
dic_19720401
---
Date: 1972-04-01
type: literature
reading-date: 20-09-2022
date_format : dd-MM-yyyy
---
folder: 05
- filename :
dic_19720501
---
Date: 1972-05-01
type: literature
reading-date: 21-09-2022
date_format : dd-MM-yyyy
---
folder: 08_wrong_month
- filename :
dic_19720801(The month of thereading-datefield is greater than 12.)
---
Date: 1972-08-01
type: literature
reading-date: 30-76-2022
date_format : dd-MM-yyyy
---
DQL10_transform_reading-date_into_yyyy-MM-dd
Summary
Main DQL
| 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 |
Notes
Summary
Q1: What is the same code to sort the notes by date(s_rDate) in descending order?
Summary_Q1
Original Example: Q1 (To be modified)
```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
```
A1:
Another Example: A1_11
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
```
Another Example: A1_12
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
```
Another Example: A1_13
```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
```
Another Example: A1_14
```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
```
Code DQL10_transform_reading-date_into_yyyy-MM-dd
Summary_code
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
```
