npgsql
DateOnly - en_GB date flipped from October to April.
Running .NET 8 with Npgsql 8.0.3. The locale on my machine is en_GB as I'm based in the UK. The PostgreSQL server’s locale in Azure is en_US.utf8 and I don’t believe I can change it.
I have some fairly simple code which is filtering a column of type "Date" in based on a DateOnly? value being passed in to a method:
The from date being specified is: 4th October 2023.
When I then call .ToQueryString() I can see that the query has changed to become instead: 10/04/2023. Which I believe is then being interpreted as 10th April 2023 instead of 4th October 2023 giving me the wrong results from the database.
I've tried changing the thread culture of my code to be en-US. Wondering if this would give me the correct result. It doesn't. Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
Is there some other trick if I cannot change the locale of the database? Is there some other setting I'd need to provide to prevent my date from being mis-interpreted?
Apologies if this is really trivial.
-- @__contactId_1='PCLIMP01'
-- @__from_Value_2='10/04/2023' (DbType = Date)
-- @__to_Value_3='10/04/2024' (DbType = Date)
SELECT j."AccountingSystemTenantId", j."JournalNumber", j."JournalLineNumber", j."AccountCode", j."AccountingSystem", j."AllocationReference", j."AllocationState", j."Amount", j."ContactId", j."ContactName", j."CreatedAt", j."CurrencyCode", j."CurrencyRate", j."DebitCredit", j."IsCustomer", j."IsSupplier", j."Period", j."SiteId", j."Source", j."SourceType", j."TransactionDate", j."TransactionDescription", j."TransactionReference", j."UpdatedAt", j."VatIndicator", j."WeekNumber"
FROM "JournalLineItems" AS j
WHERE j."AccountingSystemTenantId" = @__businessUnit_0 AND j."ContactId" = @__contactId_1 AND j."TransactionDate" >= @__from_Value_2 AND j."TransactionDate" < @__to_Value_3```
Update, I've realised if I cast both columns to DateTime, then this issue becomes fixed:
-- @__businessUnit_0='SOM'
-- @__contactId_1='PCLIMP01'
-- @__fromDateTime_2='2023-10-04T00:00:00.0000000Z' (DbType = DateTime)
-- @__toDateTime_3='2024-10-04T00:00:00.0000000Z' (DbType = DateTime)
SELECT j."AccountingSystemTenantId", j."JournalNumber", j."JournalLineNumber", j."AccountCode", j."AccountingSystem", j."AllocationReference", j."AllocationState", j."Amount", j."ContactId", j."ContactName", j."CreatedAt", j."CurrencyCode", j."CurrencyRate", j."DebitCredit", j."IsCustomer", j."IsSupplier", j."Period", j."SiteId", j."Source", j."SourceType", j."TransactionDate", j."TransactionDescription", j."TransactionReference", j."UpdatedAt", j."VatIndicator", j."WeekNumber"
FROM "JournalLineItems" AS j
WHERE j."AccountingSystemTenantId" = @__businessUnit_0 AND j."ContactId" = @__contactId_1 AND make_timestamptz(date_part('year', j."TransactionDate")::int, date_part('month', j."TransactionDate")::int, date_part('day', j."TransactionDate")::int, 0, 0, 0::double precision, 'UTC') >= @__fromDateTime_2 AND make_timestamptz(date_part('year', j."TransactionDate")::int, date_part('month', j."TransactionDate")::int, date_part('day', j."TransactionDate")::int, 0, 0, 0::double precision, 'UTC') < @__toDateTime_3
Notice how the values in the generated SQL are now both correctly October not April.
Are you sure the reversal is not purely in the parameter logging that you're seeing? Have you verified that the values are actually wrong (e.g. in the database)?
Either way, can you please put together a minimal, runnable console program that shows the problem occurring? That's always needed with any bug report.
