My database has columns defined as "Date" and others defined as "DateTime." Is there an easy way to declare that dates should remain unchanged irrespective of the timezone. While "DateTime" fields may be shown in the client's local timezone?
SQLServer Code:
CREATE TABLE Test(
MyDate date,
MyDateTime datetime,
);
INSERT INTO Test VALUES(cast('12/14/2020' as date),cast('12/14/2020' as date));
Model:
public class TestModel {
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
public DateTime? MyDate {get;set;}
[DataType(DataType.DateTime)]
[DisplayFormat(DataFormatString = "{0:MM/dd/yyyy HH:mm}")]
public DateTime? MyDateTime {get;set;}
}
Model Query (Dapper):
using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDatabase"].ToString())
return con.query<TestModel>("SELECT * FROM Test", null, null, true, 0, CommandType.Text);
View Layer Render:
<td>@Html.DisplayFor(m => item.MyDate)</td>
<td>@Html.DisplayFor(m => item.MyDateTime)</td>
If the client is in a timezone one hour behind, they see: 12/13/2020, 12/13/2020 23:00
Instead, they should see: 12/14/2020, 12/13/2020 23:00
Edit on 12/18/2020: Dapper encodes the data into TestModel with a DateTime.Kind of Unspecified. Following the example here: Get DateTime as UTC with Dapper I can tinker with the returned DateTime object. However, the TZ conversion still happens changing the Date when rendered on the client side.
If possible, I would like to implement a generic override class at the project level to enforce this as there are several hundred instances affected.
