Message Image  

Data360 Analyze

 View Only

  • 1.  Convert date field from str. to datetime.Date as: 2017-12-29

    Posted 11-18-2019 03:14

    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. 



  • 2.  RE: Convert date field from str. to datetime.Date as: 2017-12-29

    Employee
    Posted 11-18-2019 04:37

    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.

     



  • 3.  RE: Convert date field from str. to datetime.Date as: 2017-12-29

    Posted 11-18-2019 06:13

    The following is the solution to my problem:

    'If necessary you can give a hint to the auto detection mechanism by setting the 'AmbiguousDateResolutionMode' property to 'Day First'. 

    Thank you for your support. 



  • 4.  RE: Convert date field from str. to datetime.Date as: 2017-12-29

    Employee
    Posted 04-03-2020 11:17

    Very helpful. Thanks!