PG date being returned as .NET DateOnly is a problematic breaking change

[NaeemShah1990]

After upgrading to Npgsql v10, my application crashes with a date-parsing exception for PostgreSQL DATE columns that contain values formatted as dd/MM/yyyy.
The same code works perfectly in Npgsql v9, so this appears to be a regression introduced in v10 (released today).

📌 Environment

Npgsql Version: 10.x (latest)
Previous Working Version: 9.x
.NET: .NET 10
Application Type: C# WinForms application using Dapper ORM
OS: Windows 11
Database: PostgreSQL 17

❌ Exception

System.Data.DataException: Error parsing column 31 (expirydate=24/11/2025 - DateOnly)

🔎 What Happened

Our PostgreSQL table contains a DATE field (expirydate) which stores values in dd/MM/yyyy format, for example:
24/11/2025

This maps to a normal C# property:
public DateTime? ExpiryDate { get; set; }

In Npgsql v9, this works perfectly.
In Npgsql v10, the same code throws an exception.
No breaking change related to date formats is documented.
This indicates a regression in the new v10 release.

✅ Expected Behavior

Npgsql should continue supporting PostgreSQL’s valid date formats (including dd/MM/yyyy when DateStyle is configured accordingly).
Parsing rules should match v9’s behavior unless intentionally changed and documented.

🚫 Actual Behavior

Npgsql v10 now attempts strict parsing into DateOnly.
It rejects dd/MM/yyyy even though PostgreSQL stores it.
A fatal exception is thrown during data mapping.
This did not happen in any previous version.

🧪 Minimal Repro Code

var products = connection.Query<Product>(
    @"SELECT expirydate FROM products WHERE productid = 1"
).ToList();

Model

public class Product
{
    public DateTime? ExpiryDate { get; set; }
}

Value in PostgreSQL
24/11/2025

Results
v10 → exception
v9 → works normally

📝 Notes

Npgsql v10 was released only a few hours ago — this may be an early regression.
If strict DateOnly parsing was intentional, it is not documented.
Many production PostgreSQL systems store non-ISO date formats, so this breaks real-world applications.

📣 Request

Please confirm:
Is this a bug or intended behavior?
If intended, can we get a compatibility option to keep v9 date-parsing behavior?
If a bug, please fix in the next patch release (10.0.x).

Thank you!

[fugaku]

Looks like v10 introduce breaking change for date field

https://www.npgsql.org/doc/release-notes/10.0.html

[roji]

var products = connection.Query<Product>(
    @"SELECT expirydate FROM products WHERE productid = 1"
).ToList();

public class Product
{
    public DateTime? ExpiryDate { get; set; }
}

You're using Dapper here. As @fugaku mentioned above, Npgsql 10 did a breaking change to read PostgreSQL date as .NET DateOnly instead of DateTime, as it did previously (DateOnly is an exact match for date, unilke DateTime).

Change your ExpiryDate property to be a DateOnly rather than a DateTime, that should solve your problem.

I'll go ahead and close this issue as by-design, but if you're running into further issues don't hesitate to post back here.

[NaeemShah1990]

@roji
Thanks for the update, but unfortunately changing all DateTime properties to DateOnly across the entire project is not practical for real-world applications.

We are using Dapper in a large WinForms POS system with hundreds of models, DTOs, view models, and calculations that rely on DateTime (not DateOnly). Changing every model property, every calculation, every comparison, and every view binding is an extremely invasive change that could introduce regressions across the whole system.

Prior versions of Npgsql (including v9.x) always mapped PostgreSQL DATE to .NET DateTime, and this behavior has existed for many years. Npgsql 10 switching the default to DateOnly breaks this expectation and immediately breaks any existing codebase using Dapper or manual mappers.

Would it be possible to add:

• a configuration/compatibility switch to preserve the v9 behavior
  (e.g., map DATE → DateTime instead of DateOnly), OR
• a global type handler option at Npgsql level, instead of forcing every project to refactor its entire model layer?

This would make migration to Npgsql 10 feasible without requiring developers to rewrite hundreds of files and business logic.

Thanks again for your clarification — a compatibility option would help many teams migrate smoothly.

[roji]

Prior versions of Npgsql (including v9.x) always mapped PostgreSQL DATE to .NET DateTime, and this behavior has existed for many years.

That's true, but note that this was only the case since .NET simply lacked a pure date type. This was fixed back in .NET 6 (so four years ago) with the introduction of DateOnly. If what you're really working with is dates - as opposed to timestamps - then that's the type you should be using.

calculations that rely on DateTime (not DateOnly)

If you really do calculations that rely on time component, doesn't it make sense for you to hold a timestamp in the database, rather than a date? That's another possible workaround for this: you can change your date to timestamp in the database, at which point Npgsql will return Datetime again. Though again, you really should be using the type that corresponds to the actual data you're working with (dates or timestamps), both in the database and in .NET.

In any case, it's true that the breaking change aspect here is non-trivial - let me discuss this with the team and see what we can do.

[NaeemShah1990]

@roji
Thanks for the thoughtful response — really appreciated.

To clarify the practical side of this: our application is a large-scale POS system used in production by many shops. The database schema (and the business logic) was designed years before DateOnly existed in .NET, and there are now hundreds of places in code where:

• expiry dates
• manufacturing dates
• order dates
• stock dates
• reporting date ranges
• discount validity dates
• end-of-day summaries

all rely on DateTime for business calculations, reporting, UI bindings, and comparisons.

Even though DateOnly exists now, converting an established system of this size to fully adopt DateOnly would require a very large refactor touching hundreds of models, queries, calculations and UI components — with a very high risk of regression for production clients.

Regarding using timestamp instead of date:
For many domains (expiry dates, age-restriction dates, order date without time, etc.), only the calendar date matters, not the time component, so DATE is the correct type in the database. We do not want to add an artificial time component just to satisfy the client library mapping.

This is why a compatibility switch or global mapping option (DATE → DateTime), even if opt-in, would help a lot of real-world applications migrate without breaking changes.

Thanks again for considering this — a compatibility option would be incredibly valuable for existing systems.

[johnfedak]

Adding my 2c that the DateOnly breaking change is a blocker from us ever likely moving to NpgSQL 10.0 and a compatibility flag would seem appropriate here.

DateOnly support is spotty across the .Net ecosystem. If we were 100% on PostgreSQL and starting a greenfield application on .
Net 10.0 starting with DateOnly support would be appropriate.

But with a 15 year old legacy application evolved from .Net 3.x and with numerous dependencies on other database platforms (and the .Net System.DataView class- which doesn't support DateOnly filters) moving this is both an enormous effort and likely to introduce compatibility problems with other application modules.

[NaeemShah1990]

Adding my 2c that the DateOnly breaking change is a blocker from us ever likely moving to NpgSQL 10.0 and a compatibility flag would seem appropriate here.

DateOnly support is spotty across the .Net ecosystem. If we were 100% on PostgreSQL and starting a greenfield application on . Net 10.0 starting with DateOnly support would be appropriate.

But with a 15 year old legacy application evolved from .Net 3.x and with numerous dependencies on other database platforms (and the .Net System.DataView class- which doesn't support DateOnly filters) moving this is both an enormous effort and likely to introduce compatibility problems with other application modules.

Thanks for sharing this – this matches our situation exactly.

We also have a mature, real-world POS system with many years of accumulated logic depending on DateTime, Dapper, WinForms bindings and cross-database compatibility.

As you mentioned, DateOnly is still inconsistently supported across the .NET ecosystem (DataView, UI controls, reporting libraries, EF extensions etc.), so migrating an established production system to DateOnly is a very large and risky refactor.

A compatibility flag such as “UseLegacyDateTimeMapping” would make the upgrade path much safer for existing applications, while still allowing new applications to adopt DateOnly by default.

Really appreciate you raising this – it highlights that this isn’t just a one-off scenario but a common real-world migration challenge.