I am using an Android application to collect data. Currently I have this application configured to send all data to an Azure SQL Server database after sending a completed form. I have noticed that the date and time values entered in the application are not displayed correctly in the database, specifically, the time in the database is usually 4 hours ahead of the time that I entered in the database. application (EST).
The column that accepts the date and time data in the Azure database is of the "date and time" type. The format of the data that the application sends to the database is ISO 8601 with offset (2018-11-21T14: 17: 38.877-06: 00). I confirmed this format by asking the developer of the application and also assigning the value to an nvarchar column. I have also confirmed that if the application data is assigned to an nvarchar column, the time in the database matches what was entered in the application.
Things I've tried so far:
- Change the data type of the column in the Azure database to datetimeoffset (7). After entering a valid date and time in the application, 1900-01-01 00: 00: 00.000 + 00: 00 was stored in the database.
- Change the data type of the column in the Azure database to nvarchar (50). The correct date and time was assigned to the database.
- Manipulation / manual conversion of an iso 8601 string to datetime2 and datetimeoffset in SSMS. The correct datetime returned.
- Manipulate / manually convert an iso 8601 string to datetime in SSMS. He returned an error: "The conversion failed to convert the date and / or time of the character string".
- Creation of a table with columns of type nvarchar (50), datetime, datetime2, datetimeoffset and trying to insert the string iso. This resulted in the same error as the previous one.
At this point, I'm not sure how the string does it from the application to a date and time column in the database without generating an error. I can not see or modify the queries executed to insert the application data in the database.
My questions are:
- What is causing the time stored in the database to differ from the time entered in the form?
- Is there a way to correct this?