ADO.net data table is changing time

Question

I have a strange problem with ado.net data table. I have table named Test in database. It has following data

.

I used a data table to fetch this data. Following is my code:

        using (SqlConnection cnn = new SqlConnection(strConString))
        {
            cnn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select top 100 idID,dtDate from Test order by idID desc", cnn);
            DataTable dt = new DataTable();
            da.Fill(dt);

//Inserted breakpoint and viewed dt in visualizer
            da.Dispose();
        }

When I see data in quick watch it displays data as shown in following image:

I am not understanding why data table converted a PM time to AM. I tried to display data in html table, just to figure out that it is not a bug in data visualizer or quick watch window. But it is showing time as '15 Nov 2018 10:20 AM' when format "dd MMM yyyy hh:mm tt" is applied.

Any help will be appreciated.

Yeah, what time zone are you in? Are you in UTC +12 (or -12, or whatever the other side of the world from England is)? — Flydog57, Nov 06 '18 at 05:01
Looks like your application and DB server is in two different time zones. Make sure to convert the time to application's timezone when saving. Or you can convert the time to the application's timezone when displaying the time in the window. — Dumi, Nov 06 '18 at 05:39
Just to make sure, what is the data type of the `dtDate` column in the database? — Zohar Peled, Nov 06 '18 at 06:33
Yes both are in same time zone. Database is local on my development computer. Data type of dtDate is datetime — Manpreet Singh Dhillon, Nov 06 '18 at 13:51
My code and database are in same computer, but if we assume that they are on different time zones then it should not impact the result in data table. Because data table is displaying results saved in a table. So it shouldn't change the result. To make it more clear I added this table as linked table in Microsoft Access. When I double clicked the table to see data then it was showing correct time. It is surprising. — Manpreet Singh Dhillon, Nov 06 '18 at 13:59
It's a display formatting issue, not a time zone problem. My guess is your date and time formatting settings in Windows have been altered. What do they show? (From date/time settings in Windows control panel or system settings page) — Matt Johnson-Pint, Nov 06 '18 at 15:29
try doing dt.WriteXml("c:\\temp\\mydata,xml") and show us what's in the created file. — Moe Sisko, Nov 08 '18 at 00:38
better still, try: dt.WriteXml("c:\\temp\\mydata.xml", XmlWriteMode.WriteSchema); — Moe Sisko, Nov 08 '18 at 01:03
To clear doubts for time formats, created new table with one int & one datetime fields, added few rows with dummy values. And surprisingly data from this table is showing correct time. Now one thing is clear that there is no problem at coding side. The only difference between these two tables is that table 'Test' was created for load testing with this statement: 'SELECT CustID idID, Company [Name], EMails [Class], c.Address [Address] into Test FROM Customer'. Then added dtDate field. Then tried to update dtDate at front end. Now I will try to repeat all steps again to figure out reason. — Manpreet Singh Dhillon, Nov 09 '18 at 02:15

score 0 · Answer 1 · answered Nov 20 '18 at 18:02

The Visual Studio DataSet Visualizer uses the default date and time display formats specified by Windows. These can be altered in the Date and Time settings page, or in the Date and Time control panel. The "Additional settings..." in the control panel allow you to specify any custom format you like:

Based on what you've described, likely the "Long time" setting has been customized to hh:mm:ss - which is a 12 hour format without showing the am/pm designator. I recommend using either HH:mm:ss or hh:mm:ss tt instead, such that 19:57 doesn't show as 07:57 without you knowing whether it's am or pm.

ADO.net data table is changing time

1 Answers1