SqlDataAdapter not filling DateOnly fields from SQL Server in C# - Stack Overflow

I have a SQL Server table with

CREATE TABLE date_table 
(
    id int NOT NULL,
    my_date date NULL
);

And I'm fetching data into the table from a DataTable in C# with a standard call to Fill like so:

String sql = "SELECT * FROM date_table";

DataTable dt = new DataTable();
SqlCommand command = new SqlCommand(sql, connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dt);

I expected with the latest versions of C#/SQL Server that the column type in the DataTable for my_date would be DateOnly rather than DateTime. Shouldn't this work transparently now?

I understand how to parse this into a DateOnly, that's not the solution I'm looking for, I'm looking to get a DateOnly in the DataTable.

I have a SQL Server table with

CREATE TABLE date_table 
(
    id int NOT NULL,
    my_date date NULL
);

And I'm fetching data into the table from a DataTable in C# with a standard call to Fill like so:

String sql = "SELECT * FROM date_table";

DataTable dt = new DataTable();
SqlCommand command = new SqlCommand(sql, connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dt);

I expected with the latest versions of C#/SQL Server that the column type in the DataTable for my_date would be DateOnly rather than DateTime. Shouldn't this work transparently now?

I understand how to parse this into a DateOnly, that's not the solution I'm looking for, I'm looking to get a DateOnly in the DataTable.

• c#
• sql-server
• datatable
• ado.net

Share Follow edited Jan 31 at 4:45 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 31 at 0:53 Yndigo DreamYndigo Dream 1155 bronze badges 8

• 1 Are you using Microsoft.Data.SqlClient version 5.1 or higher? erikej.github.io/dotnet/sqlclient/2022/11/17/… – Charlieface Commented Jan 31 at 1:17
• I'm using 6.0.0. – Yndigo Dream Commented Jan 31 at 4:49
• 2 DateOnly is a relatively recent addition, and DbDataAdapter/DataTable is essentially a legacy API; personally, I wouldn't expect any adapter implementations to be updated to support it - any reason you're using DataTable? In most circumstances, anything else is preferable – Marc Gravell Commented Jan 31 at 8:48
• Looks like it's not supported: AFAICT the adapter uses SqlDataReader.GetValues under the hood, see github.com/dotnet/runtime/blob/…, which means it's never going to work github.com/dotnet/SqlClient/issues/1009#issuecomment-1257094247 You'd need to loop the reader and add the rows yourself. Have you considered using a proper object model to represent your table, rather than the old style DataTable? – Charlieface Commented Jan 31 at 11:53
• All the breaking changes to Microsoft.Data.SqlClient the last couple of years and they're worried about people being mad for improving direct type mapping? Crazy stuff. – AlwaysLearning Commented Jan 31 at 12:56

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

DateOnly is a relatively recent addition, and DbDataAdapter/DataTable is essentially a legacy API.

I cannot stand Entity Framework. If it's a legacy API, what's the lowest level replacement for it?

SqlDataReader is the foundational API. You can use Dapper on top of it for a more modern high level API that isn't a full ORM.