Date fields query as '1-1-0001' even though data in source environment (ZOHO) displaying correctly

  • 347Views
  • Last Post 22 April 2019
0
votes
Sachin Dabrein posted this 04 February 2019

Hello,

We are trying to integrate data from Zoho to an Azure Database. Several records keep failing due to the error 'SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.'. When we query the data from Skyvia, we can see the date fields in question displaying '1/1/0001' for those records, however, those records display the correct dates in Zoho. Could you kindly let us know if there is any way to query the correct dates?

Best Regards,

Simran

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 04 February 2019

Dear Sachin!

Please create Export package with the tables that reproduces this behaviour. Run it and notify us about the results.

If the same behaviour is reproducible, please allow us to run this package in the test mode for deeper investigation. Also, we would be grateful if you specify records with incorrect dates and specify expected results (values from Zoho).

 

Looking forward to your reply.

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 22 February 2019

Hi Mariia, 

 

Thanks for your responce. 

I have created the export package with the same table. It showing the wrong dates for few records however, that table cotains proper data. 

 

can you please suggest me on how to allow you to test the package. 

 

Thnaks & Regards, 

Sachin Dabre

0
votes
Mariia Zaharova posted this 22 February 2019

Hi Sachin!

We just need your confirmation that we can run this package in the test mode for investigation. We will run this package several times with extended logging.

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 22 February 2019

Yes please proceed.

Thanks and Regards,

Sachin Dabre

0
votes
Mariia Zaharova posted this 22 February 2019

Hi Sachin!

 

Thank you. Please also specify the exact field names we should look at.

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 22 February 2019

Hi Mariia, 

 

the name of the feilds are to and from (date fields). 

Please let me know once you have resolved the issue. 

Thanks, 

Sachin

0
votes
Mariia Zaharova posted this 25 February 2019

Hi Sachin!

 

Definitely, I will let you know the results as soon as possible.

One more question - what dates for these failed records do you see in Zoho (specify just a few for an example)?

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 28 February 2019

Hi Mariia, 

Any update on the issue. 

Please work on this issue on priority. This is really very urgent since the issue is opend from last four weeks. 

I am available on call to discuss on the issue. 

 

Let me know in case of any query. 

 

Thanks and Regards, 

Sachin Dabre

0
votes
Mariia Zaharova posted this 01 March 2019

Hi Sachin!

 

The investigation of this issue is in progress. I will definitely let you know the results as soon as any are available.

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 04 March 2019

Hi Mariia, 

 

What is the progress? 

Can you please work on this case on priority. this case is open since last few weeks. 

 

Thanks, 

Sachin

0
votes
Sachin Dabrein posted this 11 March 2019

any update on this?

 

0
votes
Mariia Zaharova posted this 13 March 2019

Sorry for the delayed response.

 

We have investigated the issue with reading dates from Zoho People. The issue is related to the Date Format settings. Skyvia accepts only InvariantCulture format (mm/dd/yyyy) at the moment. In your case we receive values like this one "18/01/2019" (dd/mm/yyyy). In order to resolve the issue please change Date Format in Zoho People company settings:

https://people.zoho.com/devtest/zp#admin/organization/companysettings

 

We will continue working on this behaviour and implementing fix for this case and inform you  about the results as soon as any are available.

 

Thank you for your patience.

 

Best regards,

Mariia

0
votes
Mariia Zaharova posted this 19 March 2019

Hi Sachin!

 

We have added new connection parameter to the Zoho People connection: DateTime Format. You can specify here datetime format according to the used one in your Zoho People company settings.

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 19 March 2019

Cool. 

Let me check it. 

Thanks, 

Sachin 

0
votes
Sachin Dabrein posted this 19 March 2019

I am getting the same data. issue is still not resolved. 

i have entered the date format as "dd/mm/yyyy". is this the right way of writing? 

else please suggest how it is working. 

 

Thanks & Regards, 

Sachin 

0
votes
Mariia Zaharova posted this 20 March 2019

Hi Sachin!

 

DateTime Format must be the same as it is specified in your Zoho People company settings. For example:

Zoho People connection in Skyvia:

Zoho People company settings:

 

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 25 March 2019

I did exactly the same still its giving me an error.

Please suggest for the further step.

 

Thanks,

Sachin

0
votes
Mariia Zaharova posted this 26 March 2019

We are investigating the issue. I will post here the results as soon as possible.

 

Best regards,

Mariia

0
votes
Sachin Dabrein posted this 26 March 2019

Thanks waiting for further update.

Thanks,

Sachin Dabre

0
votes
Sachin Dabrein posted this 04 April 2019

Any Update?

Thanks,

Sachin

Show More Posts
Close