Value of datetime in DataTable object is getting changed when consuming ASMX service

Question

Value of datetime in DataTable object is getting changed when consuming ASMX service.

Code to send DataTable to ASMX service:

Dim AM As AMService_RM.AMService = New AMService_RM.AMService()

Dim dbSQLCommand1 As SqlCommand = New SqlCommand("ProcPhoenixGambroMachinedata", New SqlConnection(_constr)) 
dbSQLCommand1.CommandType = CommandType.StoredProcedure
dbSQLCommand1.Parameters.Add(New SqlParameter("@Operation", SqlDbType.VarChar)).Value = "SelectPhoenixService"
dbSQLCommand1.Connection.Open()
Dim dbsda As SqlDataAdapter = New SqlDataAdapter(dbSQLCommand1)
Dim dtTable As New DataTable()
dtTable.TableName = "MachineData" 
dbsda.Fill(dtTable)
dbSQLCommand1.Connection.Close()

If dtTable.Rows.Count > 0 Then
     rowCount = Convert.ToInt32(dtTable.Rows.Count)
     i = AM.InsertMachineDataAsDataTable(dtTable)

DataTable has one column named DataInsertedDate in which I am assigning value using getdate() at sql procedure. However when this value reaches ASMX service this getting changed.

If at service consumer end DataInsertedDate was 11/28/2023 03:05:00 then at ASMX service end it is showing 11/28/2023 04:05:00.

Code to consume this request at ASMX service:

<WebMethod(Description:="Insert Machine Data With DataSet")>

Public Function InsertMachineDataAsDataTable(ByVal dt As DataTable) As Integer
    Dim SoftID As String = "NonASP"
    _strConn = System.Configuration.ConfigurationManager.AppSettings("SoftID")
    _ConStr = GetDecryptedConnStrings(_strConn)
    Dim i As Integer = 0
    Dim dbSqlConnection As SqlConnection = New SqlConnection(_ConStr)
    Try
        dbSqlConnection.Open()
        'further code for any action.

I found this discrepancy by debugging on method at ASMX service end.

Consumer and ASMX service are in different timezone: Consumer is at CST timezone and ASMX service is at ET timezone.

After debugging I found this is happening due to timezone. But in depth I don't what is causing this, does this depend on DB server or ASMX service?

How datetime works in case of webservice and how can i resolve this problem?

Answer 1

I suspect that issue is due to the inclusion of a UTC offset to the DateTime values when the DataTable is serialized to send to the ASMX service.

The DataColumn class exposes the DateTimeMode Property that is a DataSetDateTime Enum value. The default value of which is 'UnspecifiedLocal`. According to the documentation:

The DateTimeMode cannot be modified after rows are added to a DataColumn, except for modifying from Unspecified and UnspecifiedLocal or vice-versa. The modification from UnspecifiedLocal to Unspecified and vice-versa is allowed as the difference between these two options is only during serialization and does not affect the storage.

A value of UnspecifiedLocal imposes the following effects:

DateTime is stored in Unspecified. If Local or Utc is assigned to a column in this mode, it is first converted into Unspecified. Serialization in this mode causes offset. This is the default behavior and is backward compatible. This option should be thought of as being Unspecified in storage but applying an offset that is similar to Local during serialization.

However, if the value is changed to Unspecified the following effect will be realized:

DateTime is always stored in Unspecified. If Local or Utc is assigned to a column in this mode, it is first converted into Unspecified. Serialization in this mode does not cause an offset.

So if the DateTimeMode property value from UnspecifiedLocal to Unspecified, the DateTie values will be serialized without any UTC offset.

The following code is one way to implement the necessary changes to the DataTable's columns.

Private Shared sub ConvertDateColumnsUnspecifiedLocalToUnspecified(dt as DataTable)
    Dim dtType As Type = GetType(DateTime)
    For Each col As DataColumn In dt.Columns
        If col.DataType is dtType andalso col.DateTimeMode=DataSetDateTime.UnspecifiedLocal then
            col.DateTimeMode=DataSetDateTime.Unspecified
        End If
    Next
End Sub 

Just call this method passing the DataTable as it argument before the DataTable is sent to the AM.InsertMachineDataAsDataTable method.