CozyRoc REST destination errors uploading due time constrains and data validation constrains

desanchez · ‎Feb 14, 2020

Hi, I'm facing some errors for date properties (even when the dates are set as date (not datetime) and Hubspot portal is set to EST (trail portal is also set at EST and works ok).

I am receiving this erros while uploading data:

"business_start_date"}},{"index":1,"propertyValidationResult":{"isValid":false,"message":"940824000000 is at 4:0:0.0 UTC, not midnight!","error":"INVALID_DATE","name":"date_of_first_funding"}},{"index":1,"propertyValidationResult"

2) Errors with custom properties:

(100) {"status":"error","message":"Errors found processing batch update","correlationId":"3f301f5d-c8b7-4bc3-9d01-b5908a6125c8","failureMessages":[{"index":5,"propertyValidationResult":{"isValid":false,"message":"MCA was not one of the allowed options: [label: \"Y\"\nvalue: \"Y\"\ndisplay_order: -1\nhidden: false\nread_only: false\n, label: \"N\"\nvalue: \"N\"\ndisplay_order: 1\nhidden: false\nread_only: false\n]","error":"INVALID_OPTION","name":"collections_cases"}}],"requestId":"d3a7291bf8238d8dc9b1dfa3cec847e7"}
at CozyRoc.SqlServer.SSIS.RestDestination.#=zDhgJv3h37HmT(ArrayList #=zN9oB5_I$BJP$, IStatus[] #=zIxQS8Fg=)
at CozyRoc.SqlServer.SSIS.RestDestination.#=zGoSSrGpEnkN5(ArrayList #=zN9oB5_I$BJP$, ArrayList #=zueeK9pltEseB)
at CozyRoc.SqlServer.SSIS.RestDestination.#=zfE9XmIUxhKFx()
--- End of inner exception stack trace ---

Any ideas to sort them out? Thanks

WendyGoh · ‎Feb 17, 2020

Hey @desanchez,

Let's work through the error one by one:

1. When looking to pass a date property value, the value must be formatted in midnight UTC. You can learn more here: How should timestamps be formatted for HubSpot's APIs?. An example: May 1 2015 would be 1430438400000 (01 May 2015 00:00:00 UTC).

2. It looks like you're attempting to pass value 'MCA' into your property - Collections Cases. I took a look into the property - Collections Cases and note that currently there's only two dropdown options - 'Y' and 'N'. If you'd like to pass 'MCA' as a value to Collections Cases property, you would need to add 'MCA' as a dropdown option value.

Hope this helps to shed some light and do let me know if you'd like further clarification on this!

desanchez · ‎Feb 20, 2020

Hey Wendy thanks for the reply.

While researching I also found the link you mentioned about the date time UTC; and also another posts about the Site settings for timezones.

Let me give you additional details:

I have four (4) attributes set as date (not datetime) in our source database (MS SQL 2012), using Cozyroc solution for SSIS integration, I set REST destination for Hubspot.

Here is the issue: 2 of the 4 attributes returns:

propertyValidationResult":{"isValid":false,"message":"1016773200000 is at 5:0:0.0 UTC, not midnight!","error":"INVALID_DATE"

And the other two returns: {"isValid":false,"message":"865137600000 is at 4:0:0.0 UTC, not midnight!","error":"INVALID_DATE".

Additional research pointed me to DST considerations while converting dates to UTC

https://stackoverflow.com/questions/2700197/sql-server-convert-date-field-to-utc.

The plain assumption is that I have to transform every single record (date property field) to be converted at UTC midnight considering the proper DST for the convertion. Instead of managing sourcing date field.
In spite of the above, I'm still wondering why this worked ok in the trial environment. I meant: no issues with date fields.

desanchez · ‎Feb 25, 2020

Issue is narrowed down to custom properties/fields using date picker as data type.

I have been sending different date/time data-conbinations; however I'm receiving the same error:

"error":"INVALID_DATE","name":"first_founding_date"}},{"index":2,"propertyValidationResult":{"isValid":false,"message":"1125892800000 is at 4:0:0.0 UTC, not midnight!" (Note: regardless of the date-time sent)

Hubspot Propietary date fields like Date of birth do not return the error.

I'm still some guidance before using date picker properties instead and replace them with single line test.

Thanks

WendyGoh · ‎Feb 25, 2020

Hey @desanchez,

When looking at property - first_founding_date and property - date_of_birth, do note that the field type is different.

Property first_founding_date is a 'Date picker' field type as such, it only store the date and the timestamp must be set to midnight UTC.

Property date_of_birth is a 'Single line text' field type as such, it doesn't have the requirement to pass a timestamp that is set as midnight UTC.

I manage to create a contact successfully with the following POST body:

{
  "properties": [
    {
      "property": "email",
      "value": "testhubspot@test.com"
    },
    {
      "property": "first_founding_date",
      "value": "1430438400000"
    }
  ]
}

using the Create a new contact | Contacts API.

The error you're seeing is because the timestamp is not set to UTC midnight. You would need to set that to UTC midnight before sending it over to property first_founding_date.

APIs & Integrations