0

I have a DataGridView (calendarDGV) that is populated based off of data pulled from a database. I need to take two columns that contain DateTime values and convert them from UTC (which is how they're stored) to the local time of the user's machine.

I have other functions that convert times that are working properly, so I suspect that it has to do with how I'm pulling the data from the DGV.

I've tested that the string I'm making from the DGV cell value is holding the correct value, so beyond that I'm unsure why it is not being recognized as valid.

Here's how I'm populating the DGV as well as a loop that I am using to change the cell values.

private void generateCalendar()
        {
            MySqlConnection con = new MySqlConnection(Program.connectDB);
            con.Open();

            string query = "";

            if (weekAppsRB.Checked)
            {
                query = $"SELECT a.appointmentId AS 'Appointment ID', a.type AS Type, DATE_FORMAT(a.start, \'%Y-%c-%d %T\') AS 'Start Time', DATE_FORMAT(a.end, \'%Y-%c-%d %T\') AS 'End Time', a.title AS Title, a.description AS Description, c.customerName AS 'Customer Name', a.location AS Location, a.contact as Contact FROM appointment a INNER JOIN customer c USING(customerId) WHERE YEARWEEK(start, 1) = YEARWEEK(CURRENT_DATE(), 1) AND a.userid = '{User.UserID}' ORDER BY start, 'Start Time'";
            }
            else if (monthAppsRB.Checked)
            {
                query = $"SELECT a.appointmentId AS 'Appointment ID', a.type AS Type, DATE_FORMAT(a.start, \'%Y-%c-%d %T\') AS 'Start Time', DATE_FORMAT(a.end, \'%Y-%c-%d %T\') AS 'End Time', a.title AS Title, a.description AS Description, c.customerName AS 'Customer Name', a.location AS Location, a.contact as Contact FROM appointment a INNER JOIN customer c USING(customerId) WHERE MONTH(start) = MONTH(CURRENT_DATE()) AND YEAR(start) = YEAR(CURRENT_DATE()) AND a.userid = '{User.UserID}' ORDER BY start, 'Start Time'";
            }

            MySqlCommand cmd = new MySqlCommand(query, con);
            MySqlDataAdapter adp = new MySqlDataAdapter(cmd);

            DataTable dt = new DataTable();
            adp.Fill(dt);

            calendarDGV.DataSource = dt;

            for (int i = 0; i < calendarDGV.Rows.Count; i++)
            {
                calendarDGV[2, i].Value = DateTimeHandler.toLocalTime(Convert.ToString(calendarDGV[2, i].Value));
            }
        }

And here is the function that I am getting an error at.

/*** Converts UTC time to Local time ***/
        public static string toLocalTime(string dateTime)
        {
            DateTime utcTime = DateTime.Parse(dateTime.ToString()); //Error: String was not recognized as valid
            DateTime localTime = utcTime.ToLocalTime();

            return localTime.ToString("yyyy-MM-dd HH:mm:ss");
        }

Note: The end users are not all in the same time zone, so local is specific to the machine that the program is running on.

Thanks in advance for any help/suggestions!

Amit Verma
  • 2,450
  • 2
  • 8
  • 21
Maravae
  • 13
  • 1
  • Hi, It would be better to convert the datetime in user's locale on client-side(javascript). you'll be having complete information about user's local or timezone. – Sulabh Agarwal Jul 22 '21 at 06:19
  • Take a look at this [link](https://stackoverflow.com/questions/54397001/converting-utc-datetime-to-local-datetime-in-c-sharp) – Hermanto Jul 22 '21 at 06:36

1 Answers1

-1

try thin in client-side (javascript)

function convertUTCDateToLocalDate(date) {
    var newDate = new Date(date.getTime()+date.getTimezoneOffset()*60*1000);

    var offset = date.getTimezoneOffset() / 60;
    var hours = date.getHours();

    newDate.setHours(hours - offset);

    return newDate;   
}
Sulabh Agarwal
  • 291
  • 1
  • 8