Here is the way that I ended up handling this. I hope that it helps someone else and I also hope that I am not putting any questionable techniques out there by putting this post together.
In my c# mvc server code my class looks similar to this...
public class GridRecord
{
public int id { get; set; }
public DateTime? opened_dt { get; set; }
public DateTime? closed_dt { get; set; }
}
Notice that I use DateTime? for the type, not DateTimeOffset?
Assuming you have your SQLCommand formatted and ready to go and your class (in my case GridRecord is the name of the class) matches the result of the sql column data, do this in your c# mvc controller method:
//...here we would set up the connection string and define a variable typed to the class (GridRecord)
//that will hold the returned data from the sqlcommand... for this variable I will use var sqlGridData
//... and we can handle any passed in values from the controller method parameters,
//like specialdata here in this area before we call the sqlcommand...
using (SqlConnection sqlconn = new SqlConnection(constring))
{
SqlCommand command = new SqlCommand(sql, sqlconn);
sqlconn.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
var dataTable = new DataTable();
dataTable.Load(reader);
if (dataTable.Rows.Count > 0)
{
var serializedMyObjects = JsonConvert.SerializeObject(dataTable);
sqlGridData = (List<GridModel.GridRecord>)JsonConvert.DeserializeObject(serializedMyObjects,typeof(List<GridModel.GridRecord>));
}
}
//add catch{} here to handle exception if desired (good practice)
finally
{
reader.Close();
}
}
The code above is really a pretty simple concept. We grab the result from a SQLCommand, store it in a datatable and then use Newtonsoft Json.NET to serialize/deserialize it to a list typed to the class (my class is GridRecord).
The next thing that I did is to set up the controller method to return a result. I used ActionResult instead of JsonResult, because JsonResult actually alters the datetime during serialization. In my case the controller method is called GetGridData.
public ActionResult GetGridData(string specialData = default(string))
{
//...perform some controller method actions here, like handling specialdata
//parameter and include the SqlCommand code with proper connectionstring info...
return Content(JsonConvert.SerializeObject(sqlGridData, new JsonSerializerSettings { DateTimeZoneHandling = DateTimeZoneHandling.Unspecified }));
}
Notice that we must use the JsonSerializerSettings to define DateTimeZoneHandling as Unspecified. This is the reason to use ActionResult rather than using JsonResult.
For the client code we have a lot more flexibility on the format that we pass to the controller method. I do suggest that you use military time when you store the data in the database. I am not going to get too detailed with the ajax call here to send the date to the server and save it in the database because it is up to you if you want to do a GET or POST to call the method, and it is also up to you how you want to set up your AJAX dataobject.
Here is an example of what I did for that piece for the controller method (notice it's okay to use JsonResult here because we are just posting data with no real expectation of a return, except maybe an error code). You might do something like this:
[HttpPost]
public JsonResult Add(string opened_dt = default(string))
{
result = 0; //you could use this to handle some error like opened_dt = ""
using (var dbContext = new entities())
{
TBL_special rowToAdd = new TBL_special(); //TBL_special is in the database and imported into your edmx model
rowToAdd = new TBL_special{ opened_dt = Convert.ToDateTime(opened_dt)};
try
{
dbContext.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
{
Exception raise = dbEx;
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
string message = string.Format("{0}:{1}",validationErrors.Entry.Entity.ToString(),validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
raise = new InvalidOperationException(message, raise);
}
}
throw raise;
}
}
return Json(result, JsonRequestBehavior.AllowGet);
}
Notice that in the code above I receive the parameter to the controller method as a string, and then use Convert.ToDateTime() to handle the date string conversion for the DateTime datatype in the database.
To format the date on the end user's browser to UTC before sending it to the controller method, in the javascript/jquery code I use moment.js:
moment.utc(moment(dateStringVariableFromInput).utc().format('YYYY-MM-DD HH:mm:ssZ')).format('MM/DD/YYYY HH:mm');
Or you can send the current date and time from the user's browser by using:
moment.utc(new Date()).format("MM/DD/YYYY HH:mm")
The format is very flexible with this solution if you would like to go with something other than MM/DD/YYYY HH:mm as long as the format you use is moment.js friendly. Like I said, you may want to stick with military time so you don't have to deal with AM/PM. Check the moment.js documentation for further information on acceptable formatting.
Once you receive the data back from the controller method and you are ready to convert the date/time from UTC back to the user's browser date time, then you use a moment.js statement like this...
moment.utc(moment(dateStringFromController).format('YYYY-MM-DD HH:mm:SS')).local().format('MM/DD/YYYY h:mm A');
UPDATE
After using the code mentioned above to convert the utc time to the local timezone for a while, I noticed that this line of code to convert to local time is not always working. I am now using a function combined with moment-timezone for the part that converts the UTC time retrieved from the controller method, to the user's local timezone
- added moment timezone latest stable npm i moment-timezone
- added moment-timezone-with-data-2012-2022.min.js to my bundle
- added a function to my js code which I found here by Rohit Parte
function utcToLocal(utcdateTime, tz) {
var zone = moment.tz(tz).format("Z") // Actual zone value e:g +5:30
var zoneValue = zone.replace(/[^0-9: ]/g, "") // Zone value without + - chars
var operator = zone && zone.split("") && zone.split("")[0] === "-" ? "-" : "+" // operator for addition subtraction
var localDateTime
var hours = zoneValue.split(":")[0]
var minutes = zoneValue.split(":")[1]
if (operator === "-") {
localDateTime = moment(utcdateTime).subtract(hours, "hours").subtract(minutes, "minutes").format("YYYY-MM-DD HH:mm:ss")
} else if (operator) {
localDateTime = moment(utcdateTime).add(hours, "hours").add(minutes, "minutes").format("YYYY-MM-DD HH:mm:ss")
} else {
localDateTime = "Invalid Timezone Operator"
}
return localDateTime
}
- Replaced the line in my js code to convert from utc to local time
Replaced
moment.utc(moment(dateStringFromController).format('YYYY-MM-DD HH:mm:SS')).local().format('MM/DD/YYYY h:mm A');
With
utcToLocal(dateStringFromController, moment.tz.guess())
