What's the difference between DbType.Time and DbType.DateTime?

[tacosontitan]

I'm trying to mark a property with a custom attribute which takes a DbType to specify what the database type is for the property, and in this case my property is a TimeSpan?:

[SqlColumn(nameof(Sample), DbType.Time)]
public TimeSpan? Sample { get; set; }

According to the documentation for DbType, this is the wrong member to mark with:

A type representing a SQL Server DateTime value. If you want to use a SQL Server time value, use Time.

I can't follow the recommended path of using SqlDbType.Time because it's not interchangeable with DbType. Furthermore, DbType defines a member named DateTime which causes further confusion:

A type representing a date and time value.

---

What's the difference between DbType.Time and DbType.DateTime?

[deeprobin]

@tacosontitan
The type 'TimeOnly' would be the most suitable naming convention. TimeSpan is the period between two points in time.

However, the type TimeOnly did not exist at the time the API DbType.Time was implemented.
According to the documentation, DbType.Time represents a DateTime.

Not even the corefx repo shows me how this was caused.
This seems to come from the early days of .NET Framework.

@SamMonoRT @ajcvickers @roji As you're the area owners of System.Data: Do you know anything about this? If not, could you search for it in your internal .NET Framework repo and maybe find something?

[huoyaoyuan]

DbType is a maps to ODBC type:

runtime/src/libraries/System.Data.Odbc/src/System/Data/Odbc/Odbc32.cs

Lines 864 to 866 in 97f5045

	case DbType.Date: return s_date;
	case DbType.Time: return s_time;
	case DbType.DateTime: return s_dateTime;

runtime/src/libraries/System.Data.Odbc/src/System/Data/Odbc/Odbc32.cs

Lines 770 to 772 in 97f5045

	private static readonly TypeMap s_dateTime = new TypeMap(OdbcType.DateTime, DbType.DateTime, typeof(DateTime), ODBC32.SQL_TYPE.TYPE_TIMESTAMP, ODBC32.SQL_C.TYPE_TIMESTAMP, ODBC32.SQL_C.TYPE_TIMESTAMP, 16, 23, false);
	private static readonly TypeMap s_date = new TypeMap(OdbcType.Date, DbType.Date, typeof(DateTime), ODBC32.SQL_TYPE.TYPE_DATE, ODBC32.SQL_C.TYPE_DATE, ODBC32.SQL_C.TYPE_DATE, 6, 10, false);
	private static readonly TypeMap s_time = new TypeMap(OdbcType.Time, DbType.Time, typeof(TimeSpan), ODBC32.SQL_TYPE.TYPE_TIME, ODBC32.SQL_C.TYPE_TIME, ODBC32.SQL_C.TYPE_TIME, 6, 12, false);

The official MSSQL documentation mentions mapping for SQL_TYPE_DATE(OdbcType.Date) and SQL_TYPE_TIMESTAMP(OdbcType.DateTime), but not SQL_TYPE.TYPE_TIME(OdbcType.Time). The MSSQL Time type is mapped with SQL_SS_TIME2.

According to https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/date-and-time-improvements?view=sql-server-ver15#use-time-as-a-distinct-data-type , SQL_TYPE_TIME maps to DBTYPE_DBTIME. Another resource I can find is nanodbc/nanodbc#18 , which suggests SQL_TYPE_TIME mapping with MSSQL time(0).

Anyway, this involves a lot of legacies with ODBC and is probably beyond .NET. I'd suggest to not trust the doc and test for what is actually mapped to the DB.