DateTime Changes On Different Timezone

Question

I run into an issue, don't know how to resolve this at the moment. So here it's, I've a date column in database as follows:

31-Oct-24

If I check with the following query:

select TO_CHAR(m.START_DATE, 'HH24:MI:SS') START_DATE from UserDetails m;

It returns the following:

00:00:00

That means there is no time part. We have an existing WPF project that gets the data along with date with the following: This is a SOAP service that gets passed to the WPF project

SOAP Service:

DataSet tempDataSet = new DataSet();

using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
{
  adapter.ReturnProviderSpecificTypes = false;
  adapter.Fill(tempDataSet);
}

tempDataSet.Tables[0].Rows[0]["START_DATE"];

In the WPF project, it has the data with the following with no conversion:

DataTable table = SRO.FindData(LoginData.Ticket, LoginData.Session, LoginData.ActAsRoleId, Where);

When the date comes into the WPF project and the client machine has UTC-5 time zone, then the date converts into this:

30-Oct-24 02:00:00

That means the date saved in database, gets converted to the system time zone. I am not sure why this is happening, I tried to convert the date to string to enforce the date (It can't be converted as it's a DateTime type) as well as force to omit the date from DateTime. But nothing prevents it to getting into system time zone. Is there any way to resolve this as I can't do any changes in the WPF project, so preferring to handle this from the SOAP Services itself?

Update 1: Here's how the DateTime is returned from the procedure.

Answer 1

Ok, so we tried to conclude this with two possible solutions that actually worked. Here are the approaches as we didn't want to change anything in the client or database:

Solution 1: To enforce actual date, the DateTime type was converted to string in a new DataTable column within the SOAP services. So that would prevent the date conversion we had earlier. Something as follows:

tempDataSet.Tables[0].Columns.Add("CALC_START_DATE_2", typeof(DateTime));
 
for (int i = 0; i < tempDataSet.Tables[0].Rows.Count; i++)
{
  tempDataSet.Tables[0].Rows[i]["CALC_END_DATE_2"] = tempDataSet.Tables[0].Rows[i]["CALC_END_DATE"];
}

Solution 2: The final solution was this, instead of creating new type, DateTime type has been used making sure that this wouldn’t change the date depending upon client’s machine or system. This piece of code ensures that - DateTimeMode = DataSetDateTime.Unspecified.

var startDate = new DataColumn("CALC_START_DATE", typeof(DateTime))
                        { DateTimeMode = DataSetDateTime.Unspecified };
                        
table.Columns.Add(startDate); 
startDate.SetOrdinal(7); //The SetOrdinal is used to make sure the order of the columns are maintained in the DataTable

I believe, in future this may save some time for developers if this kind of situation arises.