We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Fill in date range

Comments

6 comments

  • Avatar
    Douglas Robertson

    just to caveat, I'm trying to do this within in Transform node in Dataverse.

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Hi Doug,

    it is a little more convoluted in Python as the core Python does not provide a function to adjust a datetime object by a month (as it contains a variable number of days). Here is a solution that is taken from a post on StackOverflow:

    #### This code outputs multiple records with an incrementing date for each input record

    #### ConfigureFields Script

    ## Credit for custom Python function:
    ## https://stackoverflow.com/questions/35066588/is-there-a-simple-way-to-increment-a-datetime-object-one-month-in-python/35067328

    import calendar

    def add_one_month(orig_date):
      # advance year and month by one month
      new_year = orig_date.year
      new_month = orig_date.month + 1
      # note: in datetime.date, months go from 1 to 12
      if new_month > 12:
        new_year += 1
        new_month -= 12
     
      last_day_of_month = calendar.monthrange(new_year, new_month)[1]
      new_day = min(orig_date.day, last_day_of_month)
     
      return orig_date.replace(year=new_year, month=new_month, day=new_day)

    ######

    #Configure all fields from input 'in1' to be mapped
    #to the corresponding fields on the output 'out1'
    out1 += in1

    _FillInMonths = datetime.datetime(2015,2,28).date()

    ## Specify the number of records to be output
    ## for each input record (zero-based)
    i = 20

    ## Define the new date field to hold the adjusted records
    out1.FillInMonths = datetime.date


    #### ProcessRecords Script

    #Copy all fields from input 'in1' to the corresponding output fields
    #in output 'out1'. Copies the values of the fields that have been setup
    #in the mapping defined in the ConfigureFields property
    out1 += in1

    ## Set the first date value
    out1.FillInMonths = _FillInMonths
    ##explicitly write the output record
    node.write(0, out1)

    ## Now loop around outputing an adjusted date
    ## in each record
    j = 0

    while j < i:
      _FillInMonths = add_one_month(_FillInMonths)
      out1.FillInMonths = _FillInMonths
      node.write(0, out1)
      j += 1

    0
    Comment actions Permalink
  • Avatar
    Douglas Robertson

    Thanks Adrian,

    that works nicely.  I think my main problem has been figuring out how the transform node is expecting the python to be presented without it erroring.

    I think your answer will help me move forward with other aspects of phython coding in Dataverse as well so thanks.

     

    Doug

    0
    Comment actions Permalink
  • Avatar
    Stony Smith

    Just to be careful, what do you mean by "Add a month"?

    January 31 + 30 days => March 2nd

    August 31 + "1 month" will throw an error because it tries to create Sept 31.

    The code mentioned above works for DayOfMonth < 29, but has troubles for days 29,30,31

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    I agree with Stony's warning about the interpretation of adding a month. However, I just tried it with an initial date of 2015-08-31 (so that the sequence will go through the leap day in Feb 2016). The following line in the code:

    new_day = min(orig_date.day, last_day_of_month)


    causes the new date to be pegged to the last day in the new month if it is less than the value for the adjusted day of month. This means the dates will 'degrade' from 31st through 30th down to (29th or) 28th of the month as the adjusted month tracks though the year.

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    Here is a modified version of the code that will increment the date and peg the date to the last day of the new month on a month by month basis when the starting day of month for the range is greater than 28. So, if you were trying to create a range of payment dates the specified starting day of month would be used where possible and where not, the last day of the month would be used.

     

    #### ConfigureFields Script

    ## Credit for custom Python function:
    ## https://stackoverflow.com/questions/35066588/is-there-a-simple-way-to-increment-a-datetime-object-one-month-in-python/35067328
    ##
    ## Modified to allow the number of months to be optionally specified in the function call

    import calendar

    def add_n_months(orig_date, num_mths = 1):
      if not isinstance(num_mths, (int, long)):
        node.logger.error("The number of months must be an integer.")
        raise node.fail()
      if num_mths < 1:
        node.logger.error("The number of months must be at least 1.")
        raise node.fail()
      if num_mths > 11:
        years_to_add = num_mths/ 12 ## Integer division used by default
        months_to_add = num_mths % 12
      else:
        years_to_add = 0
        months_to_add = num_mths
      # advance year and month by number of months
      new_year = orig_date.year + years_to_add
      new_month = orig_date.month + months_to_add
      # note: in datetime.date, months go from 1 to 12
      if new_month > 12:
        new_year += 1
        new_month -= 12
     
      last_day_of_month = calendar.monthrange(new_year, new_month)[1]
      new_day = min(orig_date.day, last_day_of_month)
     
      return orig_date.replace(year=new_year, month=new_month, day=new_day)

    ######

    #Configure all fields from input 'in1' to be mapped
    #to the corresponding fields on the output 'out1'
    out1 += in1

    _startMonth = datetime.datetime(2015,8,31).date()

    ## Specify the number of records to be output
    ## for each input record (zero-based)
    i = 20

    ## Define the new date field to hold the adjusted records
    out1.FillInMonths = datetime.date


    #### ProcessRecords Script

    #Copy all fields from input 'in1' to the corresponding output fields
    #in output 'out1'. Copies the values of the fields that have been setup
    #in the mapping defined in the ConfigureFields property
    out1 += in1

    ## Set the first date value
    out1.FillInMonths = _startMonth
    ##explicitly write the output record
    node.write(0, out1)

    ## Now loop around outputing an adjusted date
    ## in each record
    j = 0

    while j < i:
      j += 1
      _FillInMonths = add_n_months(_startMonth, j)
      out1.FillInMonths = _FillInMonths
      node.write(0, out1)
     

     

     

    0
    Comment actions Permalink

Please sign in to leave a comment.



Powered by Zendesk