Description:
when executing a query and filling/formatting a data table, a mysql date column is converted to a System.DateTime column type instead of a DateOnly column. This requires some formatting and conversion to use as date only data not to mention time data is added from out of the blue to the data table which in MHO is always a no no.
my guess this would probably be the same with time columns also.
How to repeat:
create a table with a date only column fill with valid date(s)
( YYYY-MM-DD is the format I use, would be nice if it respected format also )
c# code
using System.Data;
using MySql.Data.MySqlClient;
some methodname
{
string cs = YOUR CONNECTION STRING ;
string query = "select `date` from thedatabase";
DataTable dt = new DataTable();
MySqlConnection connection = new MySqlConnection(cs);
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
connection.Close();
da.Dispose();
Console.WriteLine(dt.Columns["date"].DataType.ToString());
Console.WriteLine(dt.Rows[0]["date"].ToString());
}
OUTPUT:
System.DateTime
"9/9/2022 12:00:00 AM"
SHOULD BE, IF TYPE RESPECTED
System.DateOnly
"9/9/2022" or and preferably as in database "2022-09-09"
Suggested fix:
since about .NET 6 the System.DateOnly type has been available I suggest using it instead of the System.DateTime type for a MySql date type column so the type is "Respected" in use and function if not name ...
this might be applicable to time columns also .. since there is also a System.TimeOnly type now too. With the same caveats i.e System.TimeOnly for MySql.Time() type
Description: when executing a query and filling/formatting a data table, a mysql date column is converted to a System.DateTime column type instead of a DateOnly column. This requires some formatting and conversion to use as date only data not to mention time data is added from out of the blue to the data table which in MHO is always a no no. my guess this would probably be the same with time columns also. How to repeat: create a table with a date only column fill with valid date(s) ( YYYY-MM-DD is the format I use, would be nice if it respected format also ) c# code using System.Data; using MySql.Data.MySqlClient; some methodname { string cs = YOUR CONNECTION STRING ; string query = "select `date` from thedatabase"; DataTable dt = new DataTable(); MySqlConnection connection = new MySqlConnection(cs); MySqlCommand cmd = new MySqlCommand(query, connection); connection.Open(); MySqlDataAdapter da = new MySqlDataAdapter(cmd); da.Fill(dt); connection.Close(); da.Dispose(); Console.WriteLine(dt.Columns["date"].DataType.ToString()); Console.WriteLine(dt.Rows[0]["date"].ToString()); } OUTPUT: System.DateTime "9/9/2022 12:00:00 AM" SHOULD BE, IF TYPE RESPECTED System.DateOnly "9/9/2022" or and preferably as in database "2022-09-09" Suggested fix: since about .NET 6 the System.DateOnly type has been available I suggest using it instead of the System.DateTime type for a MySql date type column so the type is "Respected" in use and function if not name ... this might be applicable to time columns also .. since there is also a System.TimeOnly type now too. With the same caveats i.e System.TimeOnly for MySql.Time() type