Convert date field from str. to datetime.Date as: 2017-12-29
Hi guys,
I try to convert a column of datatype str with a dateFormat like: '29-12-2017'
I try to achieve the dateFormat to: '2017-12-29' but I can't figure out with the given documentation which code will work. I tried three options:
-----------------------------------------------------------------------------------------------------
OPTION 1: ModifyFields
With the node: ModifyField but then the node converted only a part of the values. So I this is the reason why I search for working code to fix this.
OPTION 2 Code:
#configureFields
out1 += in1
out1.date_str = str
out1.format_str = str
out1.date_object = datetime.Date
# ProcessRecords
out1 += in1
date_str = in1.eas_Factuur datum.str
format_str = '%d/%m/%Y' # The format
date_object = datetime.datetime.strptime(date_str, format_str)
---------------------------------------------------------------------------------------------------------
OPTION 3 Code:
|
#configureFields out1 += in1 |
| out1.new_date = datetime.date |
| #processRecords |
| out1 += in1 |
| if in1['eas_Factuur datum'] != "": |
| out1.new_date = datetime.datetime.strptime(in1['eas_Factuur datum'],'%d-%M-%Y').date() |
| else: |
| out1.new_date = Null |
-------------------------------------------------------------------------------------------------------------
Because the ModifyField Node only converts party my dataset I tried to first remove any elements around the values with:
out1 += in1
out1.new_date = str.lstrip(in1['eas_Factuur datum'])
This does work but not supporting the next step to convert the data.
-
The Modify Fields node should be able to convert string values to a date provided your data has a consistent format.
If necessary you can give a hint to the auto detection mechanism by setting the 'AmbiguousDateResolutionMode' property to 'Day First'.
However, if your data has an inconsistent format with leading and trailing whitespace characters then you will need to remove them first so that the data format presented to the Modify Fields node is consistent. Using the strip() operator will remove both leading and trailing whitespace.
The community forum already has a number of example of using the strptime function within a Transform node which can be found using the forum's Search functionality.
However, for your situation described above I have the following comments:
1.In Option 2 the format_str pattern is '%d/%m/%Y' this should be '%d-%m-%Y' to align with the stated format of your data
2. In Option 2 the date_object is defined as being a date type object. However, you have an upper-case 'D' in the metadata specification: datetime.Date - this should be datetime.date. Also in the ProcessRecords script the final line should be
date_object = datetime.datetime.strptime(date_str, format_str).date()
This is because the strptime() function generates a Python datetime object.
3. In Option 3: your format pattern is not correct. Instead of '%d-%M-%Y' it should be '%d-%m-%Y'
4. Option 2 provides no support for the handling of empty strings or Null values. In Option 3 you provided support for empty strings but not for Null values.
So, below is my version of the code:
#### ConfigureFields Script ####
out1 += in1
out1.new_date = datetime.date
format_str = '%d-%m-%Y' # The format
#### End of ConfigureFields Script ####
#### ProcessRecordsScript ####
out1 += in1
if in1['eas_Factuur datum'] == '' or fields['eas_Factuur datum'] == Null:
out1.new_date = Null
else:out1.new_date = datetime.datetime.strptime(fields['eas_Factuur datum'], format_str).date()
#### End of ProcessRecordsScript ####
Note, as the format_str is a constant value for all records it was defined in the ConfigureFields script.
A final general comment - rather than editing your original post, if you have additional information it is better to add a new comment. Thanks.
Please sign in to leave a comment.
Comments
3 comments