DateOnly is not supported

[Hugo-C]

DateOnly, introduced in .NET 6 is not supported as it raises:

System.NotSupportedException : Don't know about System.DateOnly

I am interested in implementing it if I could get some pointers (maybe good starting points are #239 or #1012 ?) and the assurance it'll be reviewed.

[Bediruna]

else if (clrType == typeof(DateOnly)) { return "date"; }

in the SqlType method would fix this. But it needs to be in an updated library.

[DamianSuess]

Just ran into the same issue here as well. This may help the search engines find this issue report.

System.NotSupportedException: 'Don't know how to read System.DateOnly'

Example

CREATE TABLE User (
  [Id] INTEGER PRIMARY KEY NOT NULL,
  [Name] VARCHAR(25) NOT NULL,  -- Display name
  [DateOfBirth] DATE NULL);

public class User
{
  public int Id { get; set; }
  public string Name { get; set; }
  public DateOnly? DateOfBirth { get; set; }
}

public User? GetUser(int userId)
{
  var query = "SELECT Name, DateOfBirth FROM User WHERE Id = ?";
  var result = await Connection.QueryAsync<User>(query, userId);
  // ...
}

Similar Issues

• Add support for DateOnly and TimeOnly (SqlParameter value and GetFieldValue(Async) ) dotnet/SqlClient#1813 (fixed)
• Implement DateOnly/TimeOnly support for Sqlite  dotnet/efcore#24989 (fixed)
• Introduce Date and Time only structs dotnet/runtime#49036 (Introduction of DateOnly feature)
• implement DateOnly/TimeOnly DapperLib/Dapper#2051

[TheWatchfulOne]

Sqlite itself does not have a date data type. It supports text, integer, real, null, and blob. I suggest declaring your DateOfBirth column as text and storing an ISO date string. Alternatively, you could declare it as an integer and store Unix seconds (I think?) You can declare your column as a "date" but I think it will then store the data as a blob because it does not know what a date is. This page provides an in-depth explanation.

[DamianSuess]

It is possible to incorporate and convert as pointed out by the PRs in the previous comment. As a further example, the following is performed against, DateTime itself. This library would need to incorporate a similar converter for DateOnly and TimeOnly.

		public static string SqlType (TableMapping.Column p, bool storeDateTimeAsTicks, bool storeTimeSpanAsTicks)
		{
		//...
			else if (clrType == typeof (DateTime)) {
				return storeDateTimeAsTicks ? "bigint" : "datetime";
			}
		//...
		}

		// ...

		internal static void BindParameter (Sqlite3Statement stmt, int index, object value, bool storeDateTimeAsTicks, string dateTimeStringFormat, bool storeTimeSpanAsTicks)
		{
		//...
				else if (value is DateTime) {
					if (storeDateTimeAsTicks) {
						SQLite3.BindInt64 (stmt, index, ((DateTime)value).Ticks);
					}
					else {
						SQLite3.BindText (stmt, index, ((DateTime)value).ToString (dateTimeStringFormat, System.Globalization.CultureInfo.InvariantCulture), -1, NegativePointer);
					}
				}
		//...
		}