SQL select query - no seconds or milliseconds in datetime

Question

I have a database with a table PhoneHistory and a column Date, which of datetime datatype.

I tried using this answer but I couldn't get it to work. Because I need a DataTable as the return type and I'm also currently using a DataAdapter.

This is what I currently have. I only get the date with hours and the minutes but not the seconds and milliseconds.

public static DataTable GetHistory()
{
    string query = "SELECT TOP 100 Date FROM PhoneHistory";

    return SelectSql(connectionString, query, "");
}

private static DataTable SelectSql(string connectionString, string query, string dtName, params object[] args)
{
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        try
        {
            con.Open();
        }
        catch (Exception ex)
        {
            Global.Log($"Es konnte keine Verbindung zur Datenbank hergestellt werden! Fehlermeldung: [{ex.Message}]");
            return new DataTable(dtName);
        }

        using (OleDbCommand command = new OleDbCommand(query, con))
        {
            for (int i = 0; i < args.Length; i++)
            {
                command.Parameters.AddWithValue($"@p{i + 1}", args[i]);
            }

            using (OleDbDataAdapter a = new OleDbDataAdapter(command))
            {
                DataTable dt = new DataTable(dtName);

                try
                {
                    a.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.ToString());
                }

                return new DataTable(dtName);
            }
        }
    }
}

I need to use a DataTable as the return type.

I want to get the dates like this (this is how it looks inside the database):

2019-07-30 10:10:16.526
2019-07-30 10:08:58.711

but I always get the dates like this (this is what I get with the code):

2019-07-30 10:10:00.000
2019-07-30 10:08:00.000

`DateTime` works. So does the equivalent `datetime` type in SQL. If you get no seconds, it means there aren't any to load. — Panagiotis Kanavos, Jul 30 '19 at 08:59
have you tried that query on the database using `Management Studio` ? — Squirrel, Jul 30 '19 at 09:00
As for the code, are you sure it does what you think it does? it loads 100 random dates, without any filtering criteria. — Panagiotis Kanavos, Jul 30 '19 at 09:01
If I look inside the database with the `Management Studio`, I get the values as I described. the correct ones, with seconds and milliseconds. but with the code i don't get those — slow, Jul 30 '19 at 09:01
@PanagiotisKanavos yes, i removed it. just for testing. i just want to see the date in the correct form — slow, Jul 30 '19 at 09:01
@sLw are you *sure* you're looking at the same rows? That query returns *random* rows (specifically, the order is unspecified so the server is free to use the cheapest way to just return 100 rows). `DateTime` works. If it didn't, hundreds of thousands of developers would have noticed 30 years ago — Panagiotis Kanavos, Jul 30 '19 at 09:02
@sLw given the fact that DateTime works, how did you *check* the contents? Did you use a Watch variable ? Did you dump the values to the string or console? Did you use an incorrect formatting string perhaps? — Panagiotis Kanavos, Jul 30 '19 at 09:04
@PanagiotisKanavos what do you mean by `DateTime works ` ? I didn't say it doesn't work. I mean that I don't get the correct values, even though they are correct in the database — slow, Jul 30 '19 at 09:04
@sLw you'll have to post code that actually reproduces the problem. "Extraordinary claims require extraordinary proof" and the code posted here just loads a DataTable in a slightly verbose way. You'll have to post the table's schema and sample data in `INSERT` form so people can actually reproduce what you claim — Panagiotis Kanavos, Jul 30 '19 at 09:05
@PanagiotisKanavos I have a DataGridView which DataSource is set to the returned DataTable from `GetHistory()`. But I also debugged and looked inside of the DataTable and all the dates are cut. there are no seconds and milliseconds — slow, Jul 30 '19 at 09:05
@sLw `I mean that I don't get the correct values` for that to happen, `DateTime`, ADO.NET and SQL Server would have to be broken. If anything , minute/second precision is a *missing* feature in SQL Server. In DB2 you can specify `HOURS TO MINUTES`. With SQL Server you get milliseconds, whether you like them or not. — Panagiotis Kanavos, Jul 30 '19 at 09:08
@sLw what does the connection string look like? And why did you use OleDbConnection instead of SqlConnection? — Panagiotis Kanavos, Jul 30 '19 at 09:09
@PanagiotisKanavos I used `OleDbConnection` because I'm not only accessing SQL Server inside the code. I'm also accessing excel sheets and other databases. That shouldn't have any difference. it's just for convenience. — slow, Jul 30 '19 at 09:11
@PanagiotisKanavos just create a table with a column of type `datetime` and fill it with a date that has seconds and milliseconds in it. Then, when using my `SelectSql()` you won't get the seconds and milliseconds. because probably the `DataAdapter` is cutting it. Maybe because of the `CurrentCulture` or something. I don't know. That's why I started this question, to get an answer as to why this happens and where exactly. There is nothing th proof because that's how it works and always worked. — slow, Jul 30 '19 at 09:18
@sLw I've done so thousands of times. No, DataAdapter isn't cutting it. No, culture has *nothing* to do with it. `DateTime` in ADO.NET and SQL Server is a binary value, like integers, doubles, decimals. It's *strings* that are affected by culture — Panagiotis Kanavos, Jul 30 '19 at 09:18
But where is the cutting happening? It has to be in this code — slow, Jul 30 '19 at 09:20
And how would i combine it with datatable and get the schema? I'm gonna try it. thanks — slow, Jul 30 '19 at 09:21
1) Just to please @PanagiotisKanavos and me, could you change your query to `SELECT TOP 2 Date FROM PhoneHistory order by 1` and run it both in your program and in Manangement Studio. Are you getting different formats for the dates ? 2) Your could doesn't give any output, are you able to provide an example with output ? — AndrewR, Jul 30 '19 at 09:28
@sLw Can you show us how you are retrieving the data from the DataTable? — Tasos Kokkinidis, Jul 30 '19 at 09:40
@TasosKokkinidis `dgdHistory.DataSource = Database.GetHistory(MYNUMBER);` — slow, Jul 30 '19 at 09:42

score 1 · Accepted Answer · answered Jul 30 '19 at 10:01

1

The DataAdapter is not 'cutting' the miliseconds off. You need to format the cell style of the column in your DataGridView. Add this after you set the DataSource.

dgdHistory.Columns[0].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss.fff"

This will format it in the way you described it.

answered Jul 30 '19 at 10:01

Tasos Kokkinidis

76
3

Yes, that was it. Thanks! – slow Jul 30 '19 at 10:36

SQL select query - no seconds or milliseconds in datetime

1 Answers1