Convert datetime column from utc to local time in a populated datatable

Question

I am trying to export some data from Sql Server in an excel file using EPPLUS passing a custom sql select statemet. This is working fine but the problem is that the datetime fields are stored in UTC in the db, instead I want to show them in local timezone format (es.GMT +2). Is there a way it can be done with minimal performance loss? It doesn't matter if the column is converted in the datatable or in the excel file with EPPLUS methods. I want that the final output displays the time in +2 hours.

I have tried adding DataColumn col.ExtendedProperties.Add("TZ", +2); but nothing changed. The DataColumn.DateTimeMode doesn't work with a populated DataTable. I am trying to replace the excel columns with formula adding a +2 hours but I couldn't figure it out. Below you can see my code.

string connString = @"Data Source=" + DataSourceName + ";Initial Catalog=" + InitialCatalog + ";User ID=" + UserID + "; Password=" + password + ";Integrated Security=SSPI;";
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            //retreive data from the report query
            SqlCommand Reportcmd = new SqlCommand(ReportQuery, conn);
            Reportcmd.CommandTimeout = 0;
            SqlDataAdapter Reportda = new SqlDataAdapter(Reportcmd);
            Reportda.Fill(ReportDtTbl);
            conn.Close();
            //var fileName = "Example-CRM-" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";

            MemoryStream stream = new MemoryStream();
            using (ExcelPackage pck = new ExcelPackage(stream))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report");
                Tempaltesheet.Cells[1, 1, 1, 100].Copy(ws.Cells[1, 1, 1, 100]);  //Cells[RowStart, ColumnStart, RowEnd, ColumnEnd ]
                int colNumber = 1;

                foreach (DataColumn col in ReportDtTbl.Columns)
                {
                    if (col.DataType == typeof(DateTime))
                    {
                        ws.Column(colNumber).Style.Numberformat.Format = "dd/MM/yyyy hh:mm:ss";
                        //DataColumn col.ExtendedProperties.Add("TZ", +2);
                    }
                    colNumber++;
                }
                ws.Cells["A2"].LoadFromDataTable(ReportDtTbl, false);
                // save our new workbook in the output directory and we are done!
                ReportExcel = pck.GetAsByteArray();
                //pck.Save();
            }
            return ReportExcel;

It the dates are stored in DateTime then no conversion is needed. Just make sure you display in local time. If the dates were stored incorrectly then you need to make changes. If you do need to make change best way is using SQL Server Management Studio and write a SQL Procedure. — jdweng, Sep 19 '19 at 15:35

score 0 · Answer 1 · answered Sep 19 '19 at 15:53

You should change your query to the database to account for local time difference compared to UTC instead of changing the datetime after the data is returned.

SELECT CONVERT(datetime, 
           SWITCHOFFSET(CONVERT(datetimeoffset, 
                                MyTable.UtcColumn), 
                        DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
   AS ColumnInLocalTime
FROM MyTable

As mentioned in the this answer, do not subtract hours: Convert Datetime column from UTC to local time in select statement

[A familiar-looking answer](https://stackoverflow.com/a/8038792/3777824) also notes that this won't adjust for DST. — LinuxDisciple, May 14 '20 at 01:29

score 0 · Answer 2 · answered Sep 19 '19 at 15:54

since it's already typeof datetime:

 TimeZoneInfo tz= TimeZoneInfo.FindSystemTimeZoneById("<timezone column?>");//"Central Standard Time"
   DateTime tzTime = TimeZoneInfo.ConvertTimeFromUtc(<utctime>, tzTime );

use IsDaylightSavingTime to check for daylight savings.

If you don't know what the local timezone is (it's not stored in a coumn) use ToLocalTime for the literal current timezone.

Convert datetime column from utc to local time in a populated datatable

2 Answers2