Hi,
Kindly requesting how to convert numerical value (Example: '1850') into Date value with only a four digit Year? No months or days required.
Everytime I convert to date, OpenRefine automatically adds '1850-01-01', but I only require year for Wikidata.
We have some great documentation on this starting here, take a look! :
Exploring data - Dates | OpenRefine
and
GREL functions - Dates | OpenRefine
In particular, we have .datePart("years")
If you have standardized ISO date formats already in your source data and don't need fancy parsing within the toDate() function, then you can try just this in GREL:
value.toDate().datePart("years")
if you already transformed your column and the values are already Date data type, then just do:
value.datePart("years")
Thank you for the swift reply and the resources provided.
value.datePart("years") is working perfectly for already changed date values.
Is there a way to convert a column with numerical values (Example: '1850') to Date Value?
None of the options in the resources or your examples seem to work? Would I need to manually convert each cell?
Did you have problems if you tried the GREL function:
value.toDate()
or generally using the Column menu dropdown to try the Common transform to change the data type to Date? Cell editing | OpenRefine
Hi,
It only seems to do this for the first value and not the rest. Do I need to select the others in some way?
Did you use the Column menu operation? Edit Cells -> Common Transform -> to Date on your column?
Or did you instead use the Edit button on a Cell?
(see my issue with some screenshots which is not for your case, but can be used as a guide to understand what I am talking about
Using Cell Edit button to change String to Date data type gives Uncaught RangeError · Issue #6534 · OpenRefine/OpenRefine (github.com)
)
A screenshot from you would certainly help us to understand better.
You can copy/paste an image in your reply, or use the Upload button.
I used Edit Cells -> Common Transform -> to Date on your column, but only the first cell is changing?
Only changed the first cell;
I have tried refreshing, closing and opening again. Any advise?
Sorry, I had assumed based on your original issue that you had a column that already had contained dates in a standard String format with yyyy-MM-dd. But now with your screenshot I see you actually have 4 digit Numbers (Integers)! Screenshots are so helpful!
To parse Strings into Dates, you can use the GREL .toDate() function as explained before at this link GREL functions | OpenRefine <-- What parts in it did not make sense to you? We can improve the docs with your feedback.
OpenRefine Date functions work on Dates or Strings. They do not work on Numbers.
That's probably something we should say directly in the first paragraph in our Docs. So I'll fix our Docs on that later.
So, in your case, you first need to undo the conversion of your String to Numbers and make them Strings again:
(you can use the Undo/Redo to select and click on an earlier step that converted them, or)
It worked perfectly!
In the GREL functions | OpenRefine I did not realise that it does that for strings.
Was worried that I would have to manually change each row.
Thanks for the patience and step-by-step instructions.
OpenRefine Date functions work on Dates or Strings. They do not work on Numbers.
The toDate() function is intended to treat integers as years, so if that's not working, it's a bug.
Tom
So what about when we have a strings with 2 different formats.. One with the year, month and day and one with only year.
The common transform to dates will give a result like this. How to avoid the adding of -01-01 along with the year.
@Gnoeee Click the docs link above that @tfmorris replied with. Reading our docs will show you lots of things you can learn. You can even Search our docs in the search bar in top-right corner of our website. Feel free to start a new thread if something isn't clear in our docs or you still have questions.
Hint: value.toDate().toString('yyyy')
I understand that the ISO 8601-compliant extended format includes the month and day in addition to the year. Therefore, when converting strings to dates, it will automatically include these elements alongside the year.
For example:
19831983-01-01T00:00:00ZIt would be helpful if the documentation page included an example that clearly distinguishes between representing just a year and a full date. This distinction can prevent confusion and ensure clarity for users working with date conversions.
So what about when we have a strings with 2 different formats.. One with the year, month and day and one with only year.
The common transform to dates will give a result like this. How to avoid the adding of -01-01 along with the year.
OpenRefine doesn't support year-only dates or dates without associated times. The only supported format currently is a fully specified datetime.
Once a string has been parsed into a date, information about the precision of the original string is lost.
Tom