MySqlDateTime has no way to indicate a date-only value

[michaeljon]

trafficstars

Software versions MySqlConnector version: 2.1.3 Server type (MySQL, MariaDB, Aurora, etc.) and version: MySQL 8.0.28 RDS .NET version: 6.0.401 (Optional) ORM NuGet packages and versions: N/A

Describe the bug Selecting a date only value via a cast, for example returns the value as a MySqlDateTime. There's no indicator that it was actually just a date column (and no, we don't know the column metadata at this point because this is a query builder).

cast(date_format(date_add(`requisitions`.`updated_at`, interval 420 minute), '%Y-%m-01') as date) `updated_at`

Exception N/A

Code sample

The underlying table's updated_at column is a datetime, but we're casting it away here.

Expected behavior It would be handy to have another data type or a flag on the MySqlDateTime that could be reflected so we'd know which accessor to call to get the value for serialization.

[bgrainger]

Are you specifying Allow Zero DateTime = True in your connection string (in order to have MySqlDateTime values returned)?

I haven't run your query to see what column type flags MySQL actually sends.

One thought I had is that MySqlConnector could return DATE as DateOnly but DATETIME (and also TIMESTAMP) as DateTime, which would allow clients to distinguish them. (This would obviously be a breaking change, and would need to be opt-in behaviour via a connection string option.) This wouldn't help if MySqlDateTime were being used; we could either introduce MySqlDateOnly, or set a flag on the object (as you suggested).

[MarkoBL]

I like the idea to return DATE as DateOnly and TIME as TimeOnly as an opt-in. I changed some of our internal code to use DateOnly and TimeOnly, but the MySqlConnectors returns always DateTime, so I have to cast the DateTime back to DateOnly/TimeOnly, which is ugly.