SqlDataAdapter not filling DateOnly fields from SQL Server in C#

Question

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.

Are you using Microsoft.Data.SqlClient version 5.1 or higher? erikej.github.io/dotnet/sqlclient/2022/11/17/… — Charlieface
– Charlieface, Commented Jan 31 at 1:17
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
– 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
– 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
– AlwaysLearning, Commented Jan 31 at 12:56

David Browne - Microsoft · Accepted Answer · 2025-02-01 15:30:17Z

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.

answered Feb 1 at 15:30

community wiki

David Browne - Microsoft

Sign up to request clarification or add additional context in comments.

1 Comment

Yndigo Dream Feb 5 at 16:29

Yeah it's the right answer, in fact reader.GetFieldValue<DateOnly> works.

Collectives™ on Stack Overflow

SqlDataAdapter not filling DateOnly fields from SQL Server in C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related