search

oracle / dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet
http://otn.oracle.com/dotnet
MIT License
408 stars 190 forks source link

.NET 6 DateOnly and TimeOnly types #248

Open JordanMarr opened 2 years ago

JordanMarr commented 2 years ago

Are the new .NET 6 DateOnly and TimeOnly types supported? (And if not, are there plans to support them?)

alexkeh commented 2 years ago

We're evaluating, but do not have any near term plans. We haven't seen too many people asking for these data types yet. If there are developers that want this feature, add a thumbs up emoji to the original request.

alexkeh commented 1 year ago

The ER number is 34509986 for this feature request.

max1997dc commented 1 year ago

Hi! some news??

alexkeh commented 1 year ago

I don't have a committed release vehicle yet for DateOnly and TimeOnly, but it is a feature the ODP.NET team definitely wants to do. When I have a planned milestone release, I will update this issue.

There's a lot of major features coming out in the 23c ODP.NET release, such as async, OpenTelemetry, Application Continuity, AQ and Transactional Event Queues, JSON Relational Duality, cloud single sign-on and configuration management, etc. We weren't able to schedule these new types in for the initial 23c release.

alexkeh commented 7 months ago

The Oracle .NET team has evaluated the DateOnly and TimeOnly features. Several questions came up in terms of how developers want to use these types with an Oracle database.

It doesn't appear that direct use of these types is applicable for Oracle EF Core. MS has added mapping support for EF Core 8 as a SQL Server only feature by updating the default mapping for Date and Time to these data types. SQL Server has equivalent date only and time only types. Oracle database doesn’t have these specific types.

The SQL server EF core provider does not allow DateOnly or TimeOnly to be retrieved from SQL Server dateTime. DateOnly/TimeOnly .NET types cannot be mapped to any of the following types other than “date” and “time” data types respectively: datetime, datetime2, datetimeoffset, and smalldatetime.

PostgreSQL has date only and time only data types. They also have default mappings to DateOnly and TimeOnly for these types, respectively.

From the SqlClient perspective (non-EF Core), it does not allow datetime to be retrieved as DateOnly or TimeOnly from the DataReader. If we use SqlClient reader.GetFieldValue() for a datetime column, then reader throws the InvalidCastException.

We don’t see SQL Server nor Npgsql DataReader getters for DateOnly and TimeOnly in their respective doc.

If you'd like ODP.NET to support DateOnly and TimeOnly, can you provide details about the specific functionality you are asking for? Do you see that equivalent functionality in other ADO.NET providers, such as SqlClient and Npgsql? Or is the request for DB level changes, such as the DB adding these DateOnly and TimeOnly data types as DB column and/or PL/SQL types?

JordanMarr commented 7 months ago

I don't currently have any active projects in Oracle. I asked because I maintain an OSS data library that supports SQL Server, Npgsql, Oracle and Sqlite. My intent was to research what I needed to implement to support Oracle. It sounds like I don't need to do anything (which is great for me). But I'm sure that other users will have more of an opinion.

I will say that I do appreciate the ability to store date / DateOnly columns in SQL Server, Npgsql and Sqlite. It is very useful to be able to side-step the complexity of time-zones when dealing with the concept of a DateOnly and is something I would definitely miss if I was working in Oracle.

alexkeh commented 7 months ago

@JordanMarr Thanks! Based on your feedback, your preference is end to end data type support in the DB and ODP.NET.

alexkeh commented 4 months ago

Since this data type requires end to end DB and client support, the DB enhancement request number is 36197982. The ODP.NET ER is 34509986 as previously stated. Note the ODP.NET ER is dependent on the DB ER.